Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
jessica_webb
Contributor III

Vlookup with maximum value in Qlikview

Hello,

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

TABLE_A

IDTypeScore
129xA

40

129xB40
129xC40
138xB32
138xC32
175xD37
175xE37

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

TABLE_B

TypeNominalActual
A3215
A4217
B2011
B3520
B5828
C3224
C4926
C5634
D2125
D2829
D4035
E3934
E4445

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:

IDTypeScoreActual
129xA

40

17
129xB4028
129xC4026
138xB3220
138xC3224
175xD3735
175xE3734

Many thanks,

Jess

Tags (1)
1 Solution

Accepted Solutions

Re: Vlookup with maximum value in Qlikview

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;


Capture.PNG

View solution in original post

12 Replies

Re: Vlookup with maximum value in Qlikview

Try this

FirstSortedValue(Max(Actual), ID, Type)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Vlookup with maximum value in Qlikview

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;


Capture.PNG

View solution in original post

jessica_webb
Contributor III

Re: Vlookup with maximum value in Qlikview

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

Re: Vlookup with maximum value in Qlikview

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
Contributor III

Re: Vlookup with maximum value in Qlikview

Hi Sunny,

Of course. This is my table in QV:

QV1.PNG

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

QV2.PNG

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

Re: Vlookup with maximum value in Qlikview

I guess try with a even smaller number:

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

jessica_webb
Contributor III

Re: Vlookup with maximum value in Qlikview

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

Re: Vlookup with maximum value in Qlikview

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

Best,

Sunny

jessica_webb
Contributor III

Re: Vlookup with maximum value in Qlikview

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):

Capture.PNG

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

Capture2.PNG

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