LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: jpstory

Hi

I have a spreadsheet coming in every day with various number of columns (mostly the same fields, but sometimes there are a few extra ones at the right end).
The task is simple: provide a summary of the number of cells populated (vs. blank) for each of the fields present in the spreadsheet.
For example, for the entire data populate of 100 records in the spreadsheet, 80 out of the 100 records have field 'Client Name' populated. The Result would be 80.

What I did, is to use one Filter Node to extract one specific field (the ones that are always there) and then use a Aggregate Node to get the result.
I have 30+ of such flow in my graph, look messy but work.

The problem is with those random fields that appear in the spreadsheet from time to time. I will have to add additional flows manually every time they are present.

Is there a good way/node that can dynamically catch all fields and aggregate all of to get the populated rate (populated cells vs. total number of cells) ?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

This might be a bit overkill, but it's my quick and dirty hack.
Attachments:
CountBlanks.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

Oh, and a trick that Stony has taught me is to pivot the data. It makes for a MUCH easier approach (see attached, where I show both). There may be a scaling issue if you have a lot of columns. Because we're pivoting, if you have X columns and Y rows initially, you'll be blowing out the data to be X*Y rows.
Attachments:
CountBlanks2.brg