topic Re: Vlookup type functionality in QlikView

Vlookup type functionality

hibhardwaj — Wed, 07 Nov 2012 22:12:38 GMT

I have a table which looks like the following:

Weight From	Weight To	Dollar
0	4	$20.38
4	6	$20.38
6	10	$21.48
10	12	$24.16
12	15	$26.77
15	19	$29.29
19	20	$31.82
20	24	$34.36
24	30	$36.87
30	35+	$38.42

I am taking weight as an input from the user. Now, I want a textbox, that should be able to tell what dollar value applies to that weight (selected by the user).

Would appreciate your help

Thanks,

Re: Vlookup type functionality

thomas_skariah — Thu, 08 Nov 2012 04:02:49 GMT

Hi,

Please find the attached application.

Regards,

Tom

Re: Vlookup type functionality

Anonymous — Thu, 08 Nov 2012 08:24:07 GMT

PFA

Hope itz wat u want......

Re: Vlookup type functionality

hibhardwaj — Thu, 08 Nov 2012 14:26:43 GMT

Hi Vaibhav,

Thanks for the reply. But actually I wanted more functionality from that QV. The user can give any values in between the ranges (From & To), and he should be getting the dollar amount associated with the entry. For example, if the user enters 17, he should get the dollar value of $29.29.

Really appreciate your help man.

Regards,

Re: Vlookup type functionality

hibhardwaj — Thu, 08 Nov 2012 14:33:18 GMT

Hi Tom,

Thanks for the response. But I am not trying here to sum up the dollar value. I want the user to plug in any weight, and my QV should provide the dollar amount associated with that weight. Now the weight can equal the 'Weight From' value or be in between 'Weight From' and 'Weight To' values. In either case, my QV should be able to provide the Dollar amount.

Example1: User enters weight=6, QV should provide $21.48

Example2: User enters weight=17, QV should provide $29.29

Appreciate your help

Regards,

Re: Vlookup type functionality

Thu, 08 Nov 2012 16:06:23 GMT

Take a look at the attached sheet. I used intervalmatch to load weights up to 500 (you can adjust the max) and then a variable for the user to input a weight.

Re: Vlookup type functionality

Thu, 08 Nov 2012 16:17:34 GMT

I got to thinking you may have users want to put decimals in for the weight, so here is an updated file with a round() function on the variable.

Re: Vlookup type functionality

hibhardwaj — Thu, 08 Nov 2012 17:37:17 GMT

Thanks Rebeccad, That was super helpful !

Now I need to understand all those crazy commands that you used

Re: Vlookup type functionality

Thu, 08 Nov 2012 17:57:22 GMT

No problem - let me know if you have any questions.