Data table analysis and using $Field as a Dimension

[#1]

If I have a table, using $Field as the dimension, how can I run an arbitrary expression on that field as a whole in a Straight Table?

For example, I have numerous tables with 10's of millions of rows, and over 20 fields each. I'm looking at seeing the "fill rate" of this table, among other metrics on a per-field basis (ex. Count of non-zero length field values: count({<[Field Name]-={"=len(trim([Field Name]))=0"}>} [Field Name]). I don't want to have to create an expression for the hundreds of Field Names that I have, and I still need the ability to slice-and-dice my data (ex. I want the above expression (count of non-zero length field values) for items that are marked "active", and in certain zip codes).  Because there are many tables that I'll need to run this on, I need something with minimal, if not zero, modification of the QVW to accommodate different source tables.

Because $Field is an island from the actual tables, is there a way to dynamically add the string value of the current dimension value, in this case the value of $Field, into my expression, so that for each row in my straight table, it will calculate on the desired field?

I've tried $($Field) and [$($Field)], but neither work from what I can see. The only solution I've found so far is:

=$(=concat(TOTAL 'if($Field=' & chr(39) & $Field & chr(39) & ',sum(if(len([' & $Field & '])>0,1,0))', ',') & concat(TOTAL right($Field&')',1)))

which, as one can guess with all the concatenation and If statements, does not scale well to my required purposes.

If found this thread (Max length of all fields), but it is still marked unanswered, and as I just started my new position, I don't have a license yet capable of opening the final poster's QVW to see if it contains a solution.

Any help would be appreciated,

~Zack

[#2 · Accepted solution]

So this took me all day yesterday and all of this morning, but after much trial and error, this seems to work:

=pick(RowNo(),$(=concat('Count([' & $Field & '])',',')))

The use of the concat function seems necessary to prioritize the processing of the field information inside of the $-expansion.

Hope this helps other people,

~Zack

[#3]

Haha, glad that helped. I spent the better part of a day bashing my head over the problem.

[b_garside]

Yep, I know the feeling. Took me a whole day or two to just create a button that will allow you to traverse over field values inside one text box using buttons like this.  <  >