Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Try this
FirstSortedValue(Max(Actual), ID, Type)
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;
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
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
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
I guess try with a even smaller number:
If(Type = Previous(Type), Alt(Previous(Nominal) + 0.000000000000001, 0), 0) as Lower
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
Hahahaha happen to all of us , but I am glad we were able to pin-point the issue
Best,
Sunny
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