Thursday, 17 September 2009

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.

7 comments:

  1. This option is available in version 9, correct ?
    With this it's possible to create associations on the fly ?

    ReplyDelete
  2. Hi Pablo,

    Yes, in theory you can create associations on the fly. You could, potentially, have a data schema with no associations at all!

    Stephen

    ReplyDelete
  3. Stephen,

    agree with your comment that this is, in essence, a "quick and dirty" solution, to get something out fast.

    My preferred solution for this sort of problem is to concatenate the two data tables - Stock and Sales, and have both in one "Data" table. This way, all the links are still intact, and there is no need in lengthy joins, resident loads, etc...

    cheers,

    Oleg

    ReplyDelete
  4. What would be the difference between:
    - Sum({} OnHand)
    - Sum(if(StockStore = Store,OnHand))

    ReplyDelete
  5. Sorry, I mean you solution
    Sum({XStockStore = P({$} Store)X} OnHand)
    and the second one?

    ReplyDelete
  6. It wouldn't work because the Store value wouldn't always return one value.

    The P() syntax will allow comparisons against multiple values.

    ReplyDelete
  7. This comment has been removed by a blog administrator.

    ReplyDelete