Replace IF in Expression with Set Analysis

We often come across chart expressions that look like this:

=sum(if(SLSTYPE='DOMESTIC',SLSTOT))

It is easy to understand and if your document is a normal size and working well then leave the expression alone. But, if the document is large and you are looking for ways to speed up chart performance then consider changing the expression by replacing the "if" in the expression with the much faster set analysis syntax. Remember that set analysis is related to the concept of QlikView selections. Set analysis syntax works by specifying temporary selections within the function. So, if we wanted to consider set analysis for our example expression we should imagine whether the expression would work ok if we removed the "if" from the expression and, instead, made a selection on the SLSTYPE field to select the value DOMESTIC. If the expression would work ok in that situation then we can change the expression to use set analysis like this:

=sum({$<SLSTYPE ={'DOMESTIC'}>} SLSTOT)

We couldn't do that if the SLSTYPE field wasn't associated with SLSTOT because selecting the value DOMESTIC would have no effect on the expression. For example, if SLSTYPE had been defined in the loadscript as a Data Island unrelated to any other data then the expression wouldn't work correctly not matter how you coded the set analysis syntax within the sum function.

Another important consideration is whether or not the report is designed for users to make their own selections on the SLSTYPE field. If users are making SLSTYPE selections then they may not like an expression that overrides their selection within the expression. If the document allows user selection on the SLSTYPE field then we should define a separate field in the loadscript that will contain the exact same values as SLSTYPE and be part of the same QlikView table and same rows. Maybe call it SLSTYPE_SA and then we can code the set analysis part of our expression using the SLSTYPE_SA field and the expression will work correctly while still respecting all other user selections.

* * *

Increasing Likelyhood of Duplicate Values While Loading Data

I was working on a QlikView document last week that loaded several kinds of sales forecast data and also actual sales for a chemical manufacturer. The forecast data came from a statistical forecasting application. The charts on the document were simple and showed the forecast data rounded to integer numbers of tons.

The document contained a lot of data and the performance was a little slow when making selections so I looked for ways to improve performance. Examining the forecast data, I saw that although the quantities in the database were expressed in tons almost every quantity included a decimal fraction. Now, QlikView is very good at compressing data and especially good at compressing data with duplicate values since it stores each unique value for a field only once. This forecast data did not contain a lot of duplicates because of the decimal fractions. I saw that if I rounded the values as they were loaded then values like 1.001, 1.00032, 1.2, etc. would all be loaded as 1. After changing the loadscript to round values to integers as the data was loaded the report file size on disk was reduced by 40% and had a corresponding reduction in memory required. The charts on the report were not affected all.

This technique would not be useful in all situations. It would cause a lot of trouble if you rounded data values on some accounting documents! But, sometimes, altering data slightly as it is loaded to increase the likelyhood of duplicate values can save memory for a QlikView document. Besides rounding (not necessarily to integers, you can round to a specific number of decimal places), you can also increase the likelyhood of duplicate values by:

• Truncating text data - keeping only the first ten characters, for example.




• Trimming text data to remove any leading or trailing blanks.




• Converting to all capital letters.
  



• Converting non-abbreviated words to standard abbreviations, like state abbreviations in address data.




• Removing the time stamp portion from date data




• Converting date data to beginning-of-week or beginning of month dates.




• Converting date data to month abbreviations if that is the level of data used in the document charts.

* * *

Using Oracle Configured for Different Countries

We load much of the data in our QlikView documents from Oracle databases. A common query technique uses the Oracle Next_Day function to aggregate data into weekly quantities. For example,

Select Next_Day(INC_DATE,2)-7, Count(distinct INCIDENT_NUM) From INC_LOG group by NextDay(INC_DATE,2)-7;

will gather a count of security incidents by week. The Next_Day function in the example should return a Monday date. But, sometimes, when the Oracle database is configured for a European business then the way Oracle numbers days of the week will be different from a database in the U.S. A European database may give Tuesday dates for that example query.

In order to make the SQL commands in a loadscript behave like they do in the U.S. I add a single SQL line to the loadscript following the ODBC Connect string that modifies the Oracle NLS setting temporarily while data is loaded into the document. The line I add after the ODBC connect string is:

SQL ALTER SESSION SET NLS_TERRITORY='America';

Does anyone have a different way of dealing with this issue?

* * *

Looking Inside a Bookmark Without Opening It

This blog post comes from a question asked on the QlikCommunity forum. The question was how do you look at the selections inside a bookmark without actually selecting and activating the bookmark?

I had never thought about how you might do that. It is not difficult to list out the possible values for a single field based on a bookmark whether the bookmark has been opened or not. For example, for a bookmark named Feb_Sales and a field named ITEM you can put a text expression like this into a text object or chart title:

=concat({Feb_Sales} distinct ITEM, ', ')
That will give you a list of the ITEM values separated by commas. Note that it is a list of Possible values, it will only be the Selected values if the bookmark includes a selection on that particular field.

This technique can be used to give the user a peek into the selected or possible field values in the bookmarks; perhaps helping a user decide which bookmark should be used.

You can also apply the selection to the ITEM field without opening the Feb_Sales bookmark. Go to a listbox or multibox entry for ITEM and type this:

=count({Feb_Sales} 1 )

and that will select the ITEM values (if none of the ITEM values exists in your current document then it will appear that all values have been selected).

If there is a field normally associated with ITEM then you can select that. For example, if the field Product_Category is normally associated with ITEM then go to a listbox or multibox entry for Product_Category and type that same bit of text and it will automatically select the Product_Category values associated with the Feb_Sales bookmark without opening the bookmark!

* * *

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

★ ★ ★