topic Re: Lookup tables in QlikView

Lookup tables

jessica_webb — Mon, 30 Nov 2015 15:12:54 GMT

Hello,

Hoping someone can help me figure out an expression I need to write.

Basically, I have a table with (amongst many others), the following three columns:

PersonID	TypeID	Actual Points
123	987	44
123	942	44
123	910	44
456	987	36
456	942	36
789	387	51
789	354	51
789	547	51

I also have an 'lookup' table, which tells me for each 'TypeID', what the predicted points should be, based on the 'Actual Points'.

That table looks something like this:

TypeID	Min Actual Points	Max Actual Points	Predicted Points
987	40	50	1050
987	30	39	1000
987	20	29	920
942	40	50	2000
942	30	39	1500
942	20	29	1000

What expression do I need to complete the final column in my initial table? It needs to look at the TypeID, and then the Actual Points, to find out the Predicted Points?
So for Person 123, they should be predicted 1050 in the first row, and 2000 in the second row.

Person 456 should be predicted 1000 in row4 and 1500 in row5.

Any suggestions greatly appreciated!

Thanks,
Jess

Re: Lookup tables

sunny_talwar — Mon, 30 Nov 2015 17:15:16 GMT

I think you will need to use IntervalMatch() here

UPDATE:

Try this script:

Table1:

LOAD PersonID,

TypeID,

[Actual Points]

FROM

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

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

Table2:

LOAD * INLINE [

TypeID, Min Actual Points, Max Actual Points, Predicted Points

987, 40, 50, 1050

987, 30, 39, 1000

987, 20, 29, 920

942, 40, 50, 2000

942, 30, 39, 1500

942, 20, 29, 1000

];

Join (Table1)

IntervalMatch([Actual Points], TypeID)

LOAD [Min Actual Points],

[Max Actual Points],

TypeID

Resident Table2;

Left Join (Table1)

LOAD *

Resident Table2;

DROP Table Table2;

Re: Lookup tables

anlonghi2 — Mon, 30 Nov 2015 22:29:12 GMT

Hello Jessica,

please try using the following script using your source file instead Lookup.xlsx.

Persons:
LOAD PersonID,
TypeID,
[Actual Points]
FROM
Lookup.xlsx
(ooxml, embedded labels, table is Foglio1);

join LOAD TypeID,
[Min Actual Points],
[Max Actual Points],
[Predicted Points]
FROM
Lookup.xlsx
(ooxml, embedded labels, table is Foglio2);

FinalTab:
load PersonID,
TypeID,
[Actual Points],
[Predicted Points]
resident Persons where ([Actual Points]>=[Min Actual Points] and [Actual Points]<=[Max Actual Points]) or isnull([Predicted Points]);

drop table Persons;

Best regards

Andrea

Re: Lookup tables

jessica_webb — Tue, 01 Dec 2015 14:41:46 GMT

Thanks Andrea.

I'm trying to implement this right now. It's slightly tricky as the first table I mentioned isn't a table that's been loaded in. It's a table that I have created in QV using several different fields from different data sources. So I can't use the first part of your script...

Re: Lookup tables

jessica_webb — Tue, 01 Dec 2015 14:43:29 GMT

Thanks Sunny.

Will give this a go. Can't use an inline for table 2 as it's actually a massive data file, but will try and use the script with a normal table load.

Re: Lookup tables

anlonghi2 — Tue, 01 Dec 2015 17:29:59 GMT

Hello Jessica,

I suppose you have a table named, for example, JESSICATABLE with the fields PersonID, TypeID, and Actual Points.

If yes, try using the following script:

join (JESSICATABLE) LOAD TypeID,
[Min Actual Points],
[Max Actual Points],
[Predicted Points]
FROM
Lookup.xlsx
(ooxml, embedded labels, table is Foglio2);

NEWJESSICATABLE:
load *
resident JESSICATABLE where ([Actual Points]>=[Min Actual Points] and [Actual Points]<=[Max Actual Points]) or isnull([Predicted Points]);

drop table JESSICATABLE;

Let me know.

Best regards

Andrea

Re: Lookup tables

sunny_talwar — Tue, 01 Dec 2015 17:32:54 GMT

Inline was just for sample. You just need to add this part for yours (with appropriate names)

Join (Table1)

IntervalMatch([Actual Points], TypeID)

LOAD [Min Actual Points],

[Max Actual Points],

TypeID

Resident Table2;

Left Join (Table1)

LOAD *

Resident Table2;

DROP Table Table2;

PersonID	TypeID	Actual Points
123	987	44
123	942	44
123	910	44
456	987	36
456	942	36
789	387	51
789	354	51
789	547	51

PersonID	TypeID	Actual Points
123	987	44
123	942	44
123	910	44
456	987	36
456	942	36
789	387	51
789	354	51
789	547	51

PersonID	TypeID	Actual Points
123	987	44
123	942	44
123	910	44
456	987	36
456	942	36
789	387	51
789	354	51
789	547	51