topic Lookup a non-joined table in QlikView

Lookup a non-joined table

danielact — Wed, 30 Nov 2011 16:36:02 GMT

I have a Pivot Table where I have a ranking function as a dimension. It returns values from 1 to 10,000. I have a table that has labels for certain values within the range. I want to set the dimension so that instead of returning the rank, it will return the label.

So, if my table is like this:

Rank Label

1 Best

5 Middle

10 Worst

for those three rankings, it should return the "Best", "Middle", or "Worst" label instead of 1, 5, or 10. The tables is not linked to anything, as the ranking is calculated in the Pivot Table, not in my data table, so I have nothing to link to.

Lookup a non-joined table

vgutkovsky — Wed, 30 Nov 2011 17:24:24 GMT

I think the simplest way is through a pick(match(. So it would look like this:

pick(match(rank(sum(Sales)),1,5,10),'Best','Middle','Worst')

Regards,

Vlad

Lookup a non-joined table

danielact — Wed, 30 Nov 2011 18:19:10 GMT

Thanks Vlad, that works but is a bit buggy. The reason it's buggy is because I sometimes have duplicates - if, on my 10,000 rows, I have two values that are the same, I can miss a value. On my testing, I didn't have a number 500 ranked - I had 499 twice, then 501, so it didn't return the value I need for 500.

Lookup a non-joined table

vgutkovsky — Wed, 30 Nov 2011 18:34:42 GMT

Well if there's a tie for #499, why would you expect there to be a #500 or for one of those values to arbitrarily be ranked lower than the other?

Lookup a non-joined table

danielact — Wed, 30 Nov 2011 18:38:14 GMT

In the rank function, for the first option, option 4 uses 499 for the first instance, then increases by one for each subsequent occurence. However, this is only behind the scenes - on the screen, you can either show the range or the lowest number. The match function seems to be only picking up the lowest number even with the option 4 selected.

I don't want to use 499 in my code because the numbers will change pretty often - and also if any filters are selected.

Lookup a non-joined table

vgutkovsky — Wed, 30 Nov 2011 18:53:30 GMT

Well rank is returned as a dual(text,num). You can try

pick(match(num(rank(sum(Sales))),1,5,10),'Best','Middle','Worst')

to maybe force it to use the numeric behind-the-scenes value.

Lookup a non-joined table

danielact — Wed, 30 Nov 2011 19:06:44 GMT

Thanks a lot, that fixed it!