Chart Adjusts to Whatever Fieldnames are Loaded

I’m often called on to validate a new data file from a client or to examine a new data extract. My first step is to load data into a copy of Steve Dark’s Data Profiler (search on quickintelligence data profiler). I’ve made a few modifications to Steve’s original document – here are a couple of the most useful.

I added a line to the loadscript that uses preceding load to add a record number to the rows being loaded. My line is added between the table label and the first line of the regular Load statement. It looks like this:

DATAFILE:  //table label
Load *, recno() as zrecno;  //this is the preceding load line
Load   //this is the beginning of your "regular" Load statement

For the second set of modifications, I added a new sheet or tab which I named Table.
On the new tab, I added a multibox and then I added, not fieldnames, but 20 expressions. You can add any number you like but 20 seemed like plenty. These are mostly copy and paste so they are not a lot of typing and you can start by copying the expression shown in this blog and pasting it into your document. The first multibox expression looks like this:

[$(=only({1<$FieldNo={1}>} $Field))]

That expression evaluates to the fieldname corresponding to field #1. Then I add a second expression to the multibox:

[$(=only({1<$FieldNo={2}>} $Field))]

You can see that in the second expression, I changed $FieldNo={1} to $FieldNo={2} so that the second expression evaluates to the fieldname corresponding to field #2. I did that for each of the 20 expressions so that the 20th expression in the multibox contains $FieldNo={20}

Then, I added a straight table chart. I used zrecno as the dimension. Then, I added 20 expressions. For the first expression, I typed this:

[$(=only({1<$FieldNo={1}>} $Field))]

And for the label, I typed this:

$(=only({1<$FieldNo={1}>} [$Field]))

Again, the rest of the expressions are similar, each time changing the $FieldNo={1} to be 2 for the second expression and its label, $FieldNo={3} for the third expression and its label and so on for each of the 20 expressions. Click OK when you’re done.

Now, you have a multibox and a straight table chart that appear as though you typed in the 20 fieldnames. But, these objects will adjust themselves to whatever fieldnames are loaded. If you load a file with less than 20 fieldnames, the “extra” ones will just be null.

You can copy and paste these objects into other QlikView documents and they will adjust themselves to whatever the first 20 fieldnames are in the document (see important info in first blog comment below). But, I think these are most useful in a document where different fieldnames can be loaded each time.

I checked Dropdown Select for all columns in the Presentation tab to make it easier for the document user to make selections on the chart. 

In order that we don’t burn up all of the memory on the computer, I added a calculation condition to the straight table chart with this expression =if(count(zrecno) < 200000,1,0) so that the chart will refuse to appear if there are more than 200,000 rows. I customized the error message for the calculation condition to say, “There’s more than 200k rows. You have to make some selections to reduce the amount of data in this chart.” A calculation condition like this is a good idea whenever you have an object that might try to show more data than is possible with the available memory.

  ★★★