Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
jessica_webb
Creator III
Creator III

Lookup tables

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:

PersonIDTypeID Actual PointsPredicted Points
12398744
12394244

123

91044
45698736
45694236
78938751
78935451
78954751

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:

TypeIDMin Actual PointsMax Actual PointsPredicted Points
98740501050
98730391000
9872029920
94240502000
94230391500
94220291000

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

6 Replies
sunny_talwar

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;


Capture.PNG

anlonghi2
Creator II
Creator II

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

jessica_webb
Creator III
Creator III
Author

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...

jessica_webb
Creator III
Creator III
Author

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.

anlonghi2
Creator II
Creator II

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

sunny_talwar

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;