vlookup function in QlikView

[#1]

QlikView folks,

I have a question of vlookup funtion in Qlikview. I see some discussions here, but what i am looking for is the expression to be used in a chart.

Here's an example

MEASURE	RATE	STAR 1	STAR 2	STAR 3	STAR 4	STAR 5
A	80%	0	0.73	0.79	0.85	0.96
B	40%	0	0.25	0.5	0.61	0.8
C	70%	0	0.43	0.64	0.74	0.83
D	22%	0	0.66	0.8	0.85	0.89

The 'RATE' is calculated in chart so it is dynamic. The Star1-5 are static. But they have different threshold.

Currently I use the expression below to get the star score.

IF(RATE<STAR2,1,IF(RATE<STAR3,2, IF(RATE<STAR4,3, IF(RATE<STAR5,4,5))))

But the data itself has 20m+ records, and there are several other tables. Nested IF statements make it very slow. I was wondering if there's way alternative to if statements.

Then i came up with the vlookup idea. Although the rate is dynamic calculated, but there are limited values, it could only be 0 to 1. If i can round the rate to 0.001 level, and have a lookup table for it. As table attached. Then all i need to do is to find the value associated with the rate( as attached Excel table). I tried to use fieldvalue and fieldindex function, but it didnt work. Maybe i did not do it right.

Also one thing i need to mention is that 'MEASURE' here is dimension. And only function with set analysis doesnt work either.

Please help.

[flipside · Accepted solution]

You can probably just create an expression to subtract the star score at each level from the rate and determine if the value is positive (1) or negative (0) using a formula like this ...

=ceil((Rate-Star1))
+ceil((Rate-Star2))
+ceil((Rate-Star3))
+ceil((Rate-Star4))
+ceil((Rate-Star5))

... rather than using if statements or lookups.

flipside

[rwunderlich]

You don't need to do a lookup, just link the tables together on the MEASURE and RATE fields. In your MESURE_REFERENCE_TEST table, change the column name of RATE_LOOKUP to RATE to match your fact table. Then the expression to retrieve the value is =STAR_LOOKUP.

-Rob

http://robwunderlich.com

[#4]

Rob,

RATE is not a dimension, so when i do this, it returns nothing but an '-'. I really hope it work, this seems so easy. Maybe i should change my reference table. But i have no idea how.

Thanks.

[#5]

Thanks, flipside. It works.

[rwunderlich]

I overlooked the fact that RATE was not dim. Great solution Flipside!

-Rob

[#7]

There are some pretty clever people on this Community, and flipside is one of them! Great solution!