Hi Everyone,

Here I’ll will talk through a common QlikView data structure mistake, often experienced by developers new to the tool and how to fix it!

QlikView associates the information by creating links between tables with identical field names. When a user makes a selection of ‘2015’ in the year field within the calendar table all the data in the dashboard (for every view, chart, etc.) is reduced to only show information associated with the selection .

No Selections Made in QlikView

A Selection Made in QlikView

Example of an Incorrect QlikView Structure

Now let’s look at an example where the associations made when making selection cause incorrect or unexpected results.

Incorrect QlikView Structure

If we make the same selection as before; ‘2015’ in the year field within the Calendar table we know the records in the Orders table will reduce to those which have an association. QlikView then follow the associations through to the Purchases table.

This can be confusing. If a customer selects ‘2015’ they would naturally expect all the information displayed to relate to that year but this isn’t the case. The dashboard will report any purchase made at any time but only for products that were sold in 2015 and not all purchases made in 2015.

Data is so easy to load into QlikView and often a new developer will see the tables connected without error and assume the data is ready. Perhaps eager to start creating lovely charts and such (well who wouldn’t be) and will hastily wish to leave the scripting element behind.

Problem is, like we have shown here, new developers will start to see strange results and may even spend large amounts of time trying to make a chart calculation work. Needless effort if we just spend a bit more time on the data structure. When speaking with clients and new QlikView developers I’ll quote the 80 / 20 rule approach to creating a dashboard. 80 percent of the time is spent on the script making sure the data structure is correct. Get that right and the front end development is extremely quick and pain free.

Fixing the QlikView Structure

There are a number of ways to correct this data issue. Here is one possible solution although its not a one size fits all, you have to review each data structure and adjust depending in your final requirements.

Correct QlikView Data Structure

The main different in this structure is that we have combined the Orders and Purchases into a single table by way of a Concatenation. This means one table is appended onto the end of another. When the field names are the same both tables will use them (ProductID for example)

I’ve added some new fields:

• %KeyDate uses the same value for Date and PurchaseDate for the Orders and Purchases tables respectively.
• TableName is now hard coded to what the original table name was, Orders and Purchases.

We could have gone slightly further and put OrderQty and PurchaseQty into the same field by renaming them both to Qty.

Now we have a table which looks like this (1 row per original table):

Correct QlikView Table Structure

If you look closely (by clicking the picture) you can see there’s a number of field values which are null. This is when the two different source tables have field names which are unique to that table. Old school database developers will see this as a bad thing, Don’t! QlikView handles null field vales very efficiently.

Now if we select a year all %KeyDates belonging to that year will be associated. So we will have information for both Orders placed  and Purchases made in 2015. Happy Days.

Hope you have enjoyed this post. As always I would love to hear your comments

Richard

PS – Further information about developing the optimum QlikView data structure can be found here and a further example of how Concatenation works can be found here