A Chart Expression That Works Differently for Totals Row

I built a QlikView document last week with a straight table chart where I needed to have some of the expressions work differently on the totals row than on the regular rows. One of the dimensions on the chart was date -- but the totals row needed to aggregate data for the most recent date only. I wrote an expression that takes advantage of the fact that the RowNo function returns null for the totals row. So, leaving out the actual calculation part, the expression looked something like this:
if(IsNull(RowNo()),
/* do the totals row calculation */ ,
/* else, do the regular calculation */)

A similar idea can be used if you want to change the color or bold the totals row. For example, you could use an expression like this for background color:
if(IsNull(RowNo()),yellow())

and make the totals row look like it is emphasized with a highlighter color.

Important note: If you use the RowNo() function in your column expression you will not be able to re-sort the chart by double-clicking on a column title. If that is a problem for your application then use if(Dimensionality()=0,... as a substitute.

* * *

Duplicate Rows in a QlikView Table

You may someday have a situation where you suspect that you have some duplicate rows in one of the tables within your QlikView document. It can be difficult to verify if this is true with a chart or tablebox -- you will only see a single row in the chart or tablebox even though there are duplicate rows in the table. The duplicates, though, often cause trouble with expressions. You may see an expression value that is two or three times larger than expected; this is often a symptom of duplicate rows in one of the tables.

To help identify the problem, add a row number field to each row in the QlikView table. That will give you at least one field that is unique on each of the duplicate rows. You can do this without reloading the entire document. Here's an example-- First, save a backup copy of the report that you are working on. Assume that the table to which you want to add a row number is named REDFLIGHTS. Add these rows to the top of your loadscript:

REDFLIGHTS_X:
Add Load RowNo() as REDFLIGHTS_RowNo, * Resident REDFLIGHTS;
Drop Table REDFLIGHTS;
Rename Table REDFLIGHTS_X to REDFLIGHTS;
Exit Script;

Then, from the menu, select File->Partial Reload This will run the five lines you added to your loadscript and it will build a new table inside the document that is identical to your original table but with a rownumber field, REDFLIGHTS_RowNo, added to the table. Now, in a tablebox that includes the rownumber field or in a chart using the key fields as dimensions and using a count(REDFLIGHTS_RowNo) expression you will be able to identify the duplicate rows in the table. At that point you will have to figure out how to eliminate or handle the duplicate rows when data is loaded. Remember to remove the five lines from the top of your loadscript before you next try to reload data.

* * *

Loading Multiple Files With Varying Fields

If you ever have a need to load multiple files into a single QlikView table and the files have similar but not identical fields then you might try the solution below. I’ve used this method where the files are historical data created at varying times and although the files have most fields in common, some of the fields have extra fields. For example, in a set of sales data files, the files for exported products might have extra fields for export license data.

Here is an example of the code from the loadscript. It loads a set of QVD files. It loads the first file normally, then loads all subsequent files using the Concatenate keyword. This allows the data, including the extra fields, to be loaded into the same QlikView table, SALES_TABLE.

set filectr=0;
for each sfile in filelist (e:\sales_history\QVD_SALES_MONTH*.qvd)
  If filectr=0 then
   SALES_TABLE:
   Load * from $(sfile) (qvd);
  Else
   SALES_TABLE:
   Concatenate Load * from $(sfile) (qvd);
  EndIf
  Let filectr=$(filectr)+1;
next sfile

★ ★ ★