Vlookup with maximum value in Qlikview

[jessica_webb]

Hello,

I have a very large data table with the following key fields:

TABLE_A

ID	Type	Score
129x	A	40
129x	B	40
129x	C	40
138x	B	32
138x	C	32
175x	D	37
175x	E	37

I need to match this to another, even larger data table with the following fields:

TABLE_B

Type	Nominal	Actual
A	32	15
A	42	17
B	20	11
B	35	20
B	58	28
C	32	24
C	49	26
C	56	34
D	21	25
D	28	29
D	40	35
E	39	34
E	44	45

What I need the script to do is match 'Type' between TABLE_A and TABLE_B; look at the 'Score', and return the 'Actual' for where the 'Score' is equal to or below 'Nominal'.

So the outcome table would be:

ID	Type	Score	Actual
129x	A	40	17
129x	B	40	28
129x	C	40	26
138x	B	32	20
138x	C	32	24
175x	D	37	35
175x	E	37	34

Many thanks,

Jess

[Anil_Babu_Samineni]

Try this

FirstSortedValue(Max(Actual), ID, Type)

[sunny_talwar · Accepted solution]

May be like this:

Table_A:

LOAD ID,

    Type,

    Score

FROM

[https://community.qlik.com/thread/233051]

(html, codepage is 1252, embedded labels, table is @1);

Table_B_Temp:

LOAD Type,

    Nominal,

    Actual

FROM

[https://community.qlik.com/thread/233051]

(html, codepage is 1252, embedded labels, table is @2);

Table_B:

LOAD *,

  Nominal as Upper,

  If(Type = Previous(Type), Alt(Previous(Nominal) + 1, 0), 0) as Lower

Resident Table_B_Temp

Order By Type, Nominal;

DROP Table Table_B_Temp;

Left Join (Table_A)

IntervalMatch(Score, Type)

LOAD Lower,

  Upper,

  Type

Resident Table_B;

Left Join (Table_A)

LOAD *

Resident Table_B;

DROP Table Table_B;

[jessica_webb]

Sunny, thank you so much!

This works almost perfectly - I really appreciate your help.

The only issue it seems to be having, is if 'Score' and 'Nominal' are the same.

So for example, if ID 129x had a Score of 42, then I get a null for 'Actual' for 'Type A'.

Any ideas what might cause this?

Thanks,

Jess

[sunny_talwar]

Hi Jessica,

I am using a Mac computer and won't be able to open my own qvw, but from the script I am unable to see why for ID 129x and a score of 42 would give a null. Would you be able to share a table box which I have included above + Lower and Upper field in it to check what is Lower and Upper limits on which intervalMatch is taking place.

Best,

Sunny

[jessica_webb]

Hi Sunny,

Of course. This is my table in QV:

And this is the matching values in the original data file for TABLE_B:

I should probably point out that I amended your script slightly, where it said:

If(Type = Previous(Type), Alt(Previous(Nominal) + 1, 0), 0) as Lower

I changed to:

If(Type = Previous(Type), Alt(Previous(Nominal) + 0.1, 0), 0) as Lower

because of the decimal numbers.

Many thanks,

Jess

[sunny_talwar]

I guess try with a even smaller number:

If(Type = Previous(Type), Alt(Previous(Nominal) + 0.000000000000001, 0), 0) as Lower

[jessica_webb]

How on earth didn't I think of that??!

Yes, that was exactly the issue - huge thanks once again. What a relief!

Very best wishes,

Jess

[sunny_talwar]

Hahahaha happen to all of us , but I am glad we were able to pin-point the issue

Best,

Sunny

[jessica_webb]

Hi Sunny,

Sorry to trouble you again, but I was wondering whether you might be able to spot where my code has gone wrong. Still getting some incorrect values.

This is the table I should be reading from (TABLE B):

And this is the results I'm seeing in QV:

I'm only expecting one value per ID, which works in most cases, except for when the score is an exact match with the score in TABLE_B. So for example, ID - 1 should give an actual of 7.7.

Any ideas?

Best wishes,
Jess