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

★ ★ ★

Export All Data From All QV Tables to Text Files

Last week one of our clients asked me to provide a method that they could use to export all of the data from within a QlikView document. They had hundreds of archived documents and they wanted to be able to export the data and use it in another application.

“Sure, No problem. I’ll email you some instructions.” I sent them instructions for exporting all of the data from all QlikView tables within a document into tab-delimited text files. This is what I sent to them:

1. Create a new folder on your laptop or server. The computer where you do this must have QlikView installed.

2. Copy the QlikView .qvw document file into your folder. This will make sure you don't accidently update the original document.

3. Double-click on the .qvw file to start up QlikView.

4. From the menu, select File->Edit Script. This opens up the Loadscript Edit Window.

5. At the very top of the loadscript, before the first line, copy and paste in these lines:

/* Export all QlikView tables to tab-delimited files */
if IsPartialReload() then
for ix= 0 to 100
let tn=trim(tablename($(ix)));
if len('$(tn)')<1 then
exit script;
end if
Store "$(tn)" into "EXPORTED_QV_$(tn).TXT" (txt, delimiter is "\t");
next ix
end if
exit script;
//
6. Close the Loadscript Edit Window by clicking the OK button in the lower right corner of the window.

7. From the menu, select File->Partial Reload

8. You don't need to save the document, but if you do save it, do not copy it back to the original location.

If everything works correctly, each of the QlikView tables will be written out as tab-delimited text files into the folder. For example, if the QlikView document contains a QlikView table named HISTCUST then it would write out a text file named, EXPORTED_QV_HISTCUST.txt. The first record will contain the field names.

Note the syntax for specifying the delimiter character. If your Store command simply includes (txt) without specifying the delimiter you will get comma-delimited text files. Use the file specification, (qvd), to get QlikView QVD files.

I would've preferred a more elegant piece of code using a Do While loop but I had trouble getting the test to run correctly and I had limited time. The code above should work fine for documents with under 100 QlikView tables.

* * *

Variable That Acts Like a User-Defined Function

QlikView document variables are really just text strings with a name. In various places in the document where expressions are used you can include a variable name and QlikView will replace the name with the text string that the variable contains. It's a simple idea that can be used in many ways.

One variation on the way to use a variable is to include an argument with the variable where the argument value is provided in the larger expression where the variable is used. The technique is useful for number, currency, or date formatting. Here is an example:

Imagine that you have a document that contains some customer data including nine-digit telephone numbers and you want to show the telephone numbers formatted in U.S. style like this (123) 456-7899.

Create a variable named telno and enter this as the variable value:

'('&left($1,3)&') '&mid($1,4,3)&'-'&right($1,4)

When we use this variable in an expression, the $1 will be replaced by the argument value. It is a text replacement operation but, because it takes place within a larger expression, the effect is like a user-defined function. In our example, the customer telephone field is named CUST_PHONE. So, we can create a chart expression like this:

$(telno(CUST_PHONE))

That will show the customer telephone number in the format we need. The real value of the technique is apparent when the expression is long or complex or uses advanced techniques like user-selected formatting.

For two arguments you can use $2 and it will be replaced by the second argument. The third argument will replace $3, and so on. For example, if you had a telephone extension number as a data field then it could be used in our example chart expression like this:

$(telno(CUST_PHONE, CUST_EXT))

and then the variable value could include $2 which would be replaced by CUST_EXT. It might look like this:

'('&Left($1,3)&') '&mid($1,4,3)&'-'&right($1,4)&' ext '&$2

This kind of variable can also be used in the loadscript. The next time you are wishing for a user-defined function you can use in the loadscript, see if this concept will do the trick.

* * *

Loading Scientific or E-Notation

I was loading data into QlikView from a large, comma-delimited text file last week. I found that some of the numeric quantities were expressed in scientific notation or E-notation. Apparently, the person who had prepared the data used a reporting tool that converted some of the very large and very small numbers into scientific notation. The scientific notation numbers contain two numbers separated by an “E” character. The notation means to take the number on the left of the value and multiply by ten raised to the power of the number on the right of the value. For example, the E-notation value of 6.02E23 means that the numeric value is 6.02 times 10 to the 23rd power. It isn’t difficult to deal with in a loadscript but this example might save you some work someday. The loadscript looked something like this:

PROMO_HIST:

LOAD MATERIAL,

WHSE,

If(index(upper(FQTY),'E')>0,

subfield(upper(FQTY),'E',1)*pow(10,subfield(upper(FQTY),'E',2)),

FQTY) as FQTY

FROM D:\Tim\Promo.csv (ansi,txt,delimiter is ',',embedded labels,msq);

That IF statement in the loadscript converted any e-notation values in the data into numbers and for regular numeric values loaded them as usual.

Later in the day, I needed to load the same data into an Oracle table using Sql Loader. Interesting that the idea is the same but the syntax is different. This is what the entry in the SqlLdr control file looked like:

FQTY "DECODE(INSTR(UPPER(:FQTY),'E'),0,:FQTY, SUBSTR(:FQTY,1,INSTR(UPPER(:FQTY),'E')-1) * POWER(10,SUBSTR(:FQTY,INSTR(UPPER(:FQTY),'E'))+1))",

★ ★ ★

Time interval between two timestamps

I built a report that loads a database table of logged event data. The database table includes a datestamp field containing the date and time of the event. I wanted my report to have a chart showing the duration in hours, minutes, and seconds between the events shown on each row of the chart. That would allow me to make selections on types of events or the source of the event and the time duration between listed events would be useful.

I created a straight-table chart with a dimension of a field called EVTLOG_ID. That single field is a unique identifier that I could sort by descending value and guarantee that the rows in my chart are in chronological order with the most recent events at the top of the chart. Then I added an expression that simply said =EVTLOG_DATE to show the timestamp field from the table. Then I added another expression like this =time(interval([EVTLOG_DATE]) - below([EVTLOG_DATE])),'h:mm:ss') to show the interval between the timestamp field and the timestamp field on the lower line in the chart. The outer function, time, makes the format show as hours, minutes, and seconds without the unneeded "AM" label.

The chart works great – figuring out the elapsed time between timestamps is a tiresome chore to do in your head.

★ ★ ★