QlikView Preserving Dates in Crosstables

Business Intelligence, QlikView

The data transformation tools in QlikView are amazing.  When I think of the hours I have spent working with data that was output by some system trying to get that data into a format that I could then START modeling.  Oh, the wasted man hours.

One of the QlikView transformation tools has the ability to take a crosstable (like a pivot table in Excel) and convert it into a straight table; a traditional flat table where data typically starts.

The only problem with this tool is that it converts all of the data in the crosstable into straight text.  This means that even if you see data that looks like a date (ex: 11/25/2015), it is really just a collection of numbers and slashes.  QlikView does not understand what these numbers represent.  This makes modeling the data a bit problematic.

No matter how you try to format these “numbers” as dates, they just stay looking like numbers.

The trick to preserving the dates is not preserving them at all.  I say that because we’re not really going to preserve the dates, we’re going to re-convert them from straight text back into dates.

All of this is done via your QlikView script.  Observe the script below:

1. Pass #1 imports the original cross table and converts it into a straight table. The “date” information is captured but rendered unusable for reporting.
2. The converted data is saved to a .QVD file to the local computer’s storage.
3. The original table is dropped from the model.
4. Pass #2 takes the saved .QVD file from Step 2 and loads it back into the model. However, this pass takes the [Date] field and runs it through a NUM# function.  This converts the straight text into a number that is now understood as a number by QlikView.  The converted dates are stored in a new field named [NewDate].  Of course, you could have named the new field [Date] to retain the original naming convention.

ALTERNATIVE APPROACH

If you do not wish to clutter up your computer’s storage with temporary .QVD files, you can skip steps 2 and 3 if you get a bit more creative with your script.

The script below modifies the strategy by instead of storing a temporary table and dropping the original table, it re-imports the first table back into itself.  The “dates” from the original table are converted in-place back into numbers.