Approximate lookup for values (as vlookup in Excel)

[piaskun79]

Hi everyone,

I am trying to make approximate lookup in QV tables.

Example scenario.

Fact table:

customer | shipment_weight

A | 2.12

B | 24.4

C | 10.0

I want to add a weight_class, depending on shipment_weight.

So I have my lookup table:

weight_greater_or_equal_to | weight_class

0 | ' up to 1'

1 | '1-5'

5 | '5-10'

10 | '10-50'

50 | 'greater then 50'

So my final table shoul look something like this:

customer	weight_class
A	1-5
B	10-50
C	10-50

I was searching forum, but I didn't find solution for my problem.

I know how tu use applymap and mapping, but this is exact match and is not suitable in this case

I am looing for something similar to Excel's vlookup functions with 4th parametr set to TRUE - approximate match.

Thanks in advance for your help!

[petter]

You should be able to use either:

• IntervalMatch: IntervalMatch
• Class function: class - script and chart function ‒ QlikView

[vineetadahiya12]

Hi

You need to use interval match. I am also practicing the same. You need to define the min and max value for your interval. Refer the attached qvw. You can also refer the PDF file for a clearer understanding.

Thanks,

Vineeta

[sunny_talwar · Accepted solution]

The script should look something like this

Fact:

LOAD * INLINE [

    customer, shipment_weight

    A, 2.12

    B, 24.4

    C, 10.0

];

LookUpTable:

LOAD * INLINE [

    weight_greater_or_equal_to, weight_class

    0, up to 1

    1, 1-5

    5, 5-10

    10, 10-50

    50, greater then 50

];

FinalLookUpTable:

LOAD *,

weight_greater_or_equal_to as Start_Weight,

Alt(Previous(weight_greater_or_equal_to)-0.0000000001, 1000) as End_Weight

Resident LookUpTable

Order By weight_greater_or_equal_to desc;

DROP Table LookUpTable;

Left Join (Fact)

IntervalMatch (shipment_weight)

LOAD Start_Weight,

End_Weight

Resident FinalLookUpTable;

Left Join (Fact)

LOAD *

Resident FinalLookUpTable;

DROP Table FinalLookUpTable;

[piaskun79]

Thank you for help.

As far as I can see, there is no solution to do this without joining tables. My fact table has dozens of millions of rows so I am afraid that this will have impact on efficiency.

In this particular case our IT dept took care of "weight classes" in DWH.

But in middle or small size table I will be using IntervalMatch

[sunny_talwar]

I would say there is not good way of doing this without joining them in the script... The alternative is to not join on the back end, but then you will need to do a Cartesian on the front end which would slow down your front end.