Where is the Vlookup functionality in QlikView?

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[tresesco]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous · Accepted solution]

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

[rajeshvaswani77]

Aggr function would be helpful.

thanks,

Rajesh Vaswani

[Anonymous]

Sure - and it is right there in my example 🙂

[Anonymous]

Michael,

Thanks. That worked perfectly.

TB