Data table analysis and using $Field as a Dimension

Tue, 08 Apr 2014 13:34:12 GMT

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

Re: Data table analysis and using $Field as a Dimension

Wed, 09 Apr 2014 15:43:46 GMT

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

Re: Data table analysis and using $Field as a Dimension

Mon, 06 Oct 2014 20:38:20 GMT

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

Re: Data table analysis and using $Field as a Dimension

b_garside — Tue, 07 Oct 2014 18:00:33 GMT

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. < >

topic Data table analysis and using $Field as a Dimension in QlikView

Data table analysis and using $Field as a Dimension

Re: Data table analysis and using $Field as a Dimension

Re: Data table analysis and using $Field as a Dimension

Re: Data table analysis and using $Field as a Dimension