topic Re: vlookup function in QlikView in QlikView

vlookup function in QlikView

Mon, 11 Mar 2013 15:09:41 GMT

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.

Re: vlookup function in QlikView

flipside — Mon, 11 Mar 2013 23:29:32 GMT

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

Re: vlookup function in QlikView

rwunderlich — Tue, 12 Mar 2013 03:48:44 GMT

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

Re: vlookup function in QlikView

Tue, 12 Mar 2013 13:14:43 GMT

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.

Re: vlookup function in QlikView

Tue, 12 Mar 2013 13:15:57 GMT

Thanks, flipside. It works.

Re: vlookup function in QlikView

rwunderlich — Tue, 12 Mar 2013 13:57:21 GMT

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

-Rob

Re: vlookup function in QlikView

Fri, 06 Feb 2015 22:16:48 GMT

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