Using Sets to avoid Key Tables

This may not appeal to purists, but it might be a good way to get out of a fix when you are in a hurry to get something in front of people.

Let's create a QlikView Script:

Sales:
Load * INLINE [
Date, Item, Store, Quantity, Price, NetValue
1/1/2009, 1, 1, 1, 2.00, 2.00
1/1/2009, 1, 2, 2, 2.00, 4.00
1/1/2009, 2, 1, 1, 2.00, 2.00
1/2/2009, 2, 2, 1, 3.00, 3.00
1/2/2009, 1, 1, 3, 3.00, 9.00
1/2/2009, 2, 2, 1, 3.00, 3.00
];

Product:
Load * INLINE [
Item, Description
1, Product A
2, Product B
];

Store:
Load * INLINE [
Store, Name
1, Store 1A
2, Store 2B
];

Now, this will create a nicely connected QlikView document. Now, I am going to introduce a Stock table that tells us the stock levels for the products in each store:

Stock:
Load * INLINE [
Store, Item, OnHand
1, 1, 55
1, 2, 33
2, 1, 23
2, 2, 12
];

This will create a nice Synthetic Key!



Now, the normal approach to avoiding the Synthetic Key is to start creating a Key table to nicely join all the tables. This, of course, uses lots of Joins and Concatenation and Resident loads (which are slow!!!). All of this means that your document loading takes a lot longer - losing any QVD optimisation that you might have.

Here is my solution to resolve this Synthetic Key:

Stock:
Load * INLINE [
StockStore, Item, OnHand
1, 1, 55
1, 2, 33
2, 1, 23
2, 2, 12
];

"Hey! You can't do that", I hear you all shouting. You have broken your links! How can you see the correct stock levels? If someone selects a Store, then the value of the stock in the chart won't change! If you chart it now, you will just get the same value because there is no association.

And this, of course, is true. If I, for example, create a Straight Table with a dimension of "Description" (the Product Name) and use the expression:

Sum(OnHand)

I will get the 78 for Product A and 45 for Product B. Now, if I select Store 1A, the values will not change - which is incorrect.

But how about if I use this expression:

Sum({<StockStore = P({$} Store)>} OnHand)

Now the values do change if I make selections on Store.

This is using the new "P" option in Set Analysis to allow me to make a set of the relative values. So, my "P" returns a set of the selected Stores in this document and then I am using that to filter the StockStore value.

One downside - and I would love to hear from someone who can overcome it - is that it doesn't work if the dimension is Store instead of Product. I need to work on that.