Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | Predicted 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
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;
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
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...
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.
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
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;