topic Re: Where is the Vlookup functionality in QlikView? in QlikView

Where is the Vlookup functionality in QlikView?

Anonymous — Thu, 15 Aug 2013 18:00:37 GMT

I'm summing sales over a dimension and want to look up a Score (third column) that falls between two values (first two columns) as shown in the table below. I cannot use intervalmatch as I'm looking up values aggregated after the load. I cannot use the lookup function as there are no exact matches. How do I do something like this in QlikView? I haven't been able to find the Excel analog to Vlookup in the forums or documentation.

Min	Max	Score
0	10	0
11	50	25
51	100	50
101	250	75
251	1000	100

Re: Where is the Vlookup functionality in QlikView?

Anonymous — Thu, 15 Aug 2013 18:02:42 GMT

Sorry, I meant to say where the sum of the sales aggregated over the dimensions falls between the Min and Max for a particular score.

Re: Where is the Vlookup functionality in QlikView?

Anonymous — Fri, 16 Aug 2013 00:05:29 GMT

Tom,

I'm not sure what exactly you need... Anyway take a look at the column(n) function, maybe it will help. Or upload an example.

Regards,

Michael

Re: Where is the Vlookup functionality in QlikView?

tresB — Fri, 16 Aug 2013 04:02:36 GMT

I usually use FisrtSortedValue() function as alternative to lookup function.

Re: Where is the Vlookup functionality in QlikView?

Anonymous — Fri, 16 Aug 2013 15:25:39 GMT

Michael,

I've attached an example of how I would do this in Excel. Tables 1 (Product, Type, Sales) and 2 (Product, Ingredient) would be loaded into QlikView and joined on the Product field as represented in the third table. Table 4 (Min Sales, Score) would be loaded separately in QlikView and would be used to score the aggregate sales for each ingredient, potentially based on a selection of Product Type. You can see the aggregated sales by ingredient in Table 5 (which would be a Straight table in QlikView). In the Score column, you can see how I've used the Excel Vlookup function to find a Score in the Lookup table (table 4) where the aggregated sales by ingredient (potentially limited by a selection of Product Type) are greater than the first Min Sales value sorted in ascending order.

I don't see a clear way to do this in QlikView.

Tom

Re: Where is the Vlookup functionality in QlikView?

Anonymous — Fri, 16 Aug 2013 19:44:22 GMT

I see now what you want to get the greatest Score value where MinSales doesn't exceed Total result. The logic (not the solution) is like this:

max(if(MinSales<=Total) Score))

I'll try to find time to come up with an example based on your table.

Regards,

Michael

Re: Re: Where is the Vlookup functionality in QlikView?

Anonymous — Mon, 26 Aug 2013 15:16:52 GMT

Found time for this after all... See attached, based on your Excel file.

The expression is:

max(if([Min Sales]<=aggr(sum(Sales),Ingredient, [Min Sales]), Score))

Regards,

Michael

Re: Where is the Vlookup functionality in QlikView?

rajeshvaswani77 — Mon, 26 Aug 2013 15:19:44 GMT

Aggr function would be helpful.

thanks,

Rajesh Vaswani

Re: Where is the Vlookup functionality in QlikView?

Anonymous — Mon, 26 Aug 2013 15:21:39 GMT

Sure - and it is right there in my example 🙂

Re: Where is the Vlookup functionality in QlikView?

Anonymous — Tue, 27 Aug 2013 04:37:04 GMT

Michael,

Thanks. That worked perfectly.

Re: Where is the Vlookup functionality in QlikView?

Tue, 27 Aug 2013 05:47:31 GMT

hey Hi,

why not to use left join in the back end script, tht will give the desired O/P of VlookUp in any scenario.

Re: Where is the Vlookup functionality in QlikView?

Anonymous — Tue, 27 Aug 2013 12:49:51 GMT

I'm trying to loosely link an expression (with set analysis) result with a data island. How do you do that with a left join even at the back end of a scrip to an expression result? In 99.9% of the cases, moreover, there will actually be no match to the fields in the data island. I'm looking for a match that is closest to the expression result without being greater than the result.