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'.
Realise it's a bit cheeky to come back for some advice after all this time, but wondered whether you might be able to help?
The code that you created has been working perfectly all this time, but I've just hit upon an issue that I hadn't anticipated happening!
Using my original example:
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
The issue that I hadn't thought of is that there are occasions where the 'Score' in TABLE_A, is higher than the maximum 'Nominal' in TABLE_B. So for example, if I had a row in TABLE_A that read:
ID
Type
Score
129x
A
45
then I would get an error, as there is nothing in TABLE_B that would match this. So I need another line of code somewhere that allows for this anomaly and then drops back down to the closest 'Nominal'. In this case, that would be 42, with an 'Actual' of 17.
I'm so sorry that I'm not able to suggest what part of the script to change, or how to change it, but I'm quite out of my depth with this one!
(html, codepage is 1252, embedded labels, table is @2);
Concatenate (Table_B_Temp)
LOAD Distinct Type,
'100' as Nominal
Resident Table_B_Temp;
Table_B:
LOAD Type,
Nominal,
If(Nominal = 100, Peek('Actual'), Actual) as Actual,
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;
Here the key would be to decide an appropriate value for Nominal (in red). You don't want it to be too big because the performance will suffer and you don't want it to be too small that it isn't able to cover a higher value. Like in your example, you showed 45... but I assumed that it would never cross 100. If that is not true, you might want to go with a slightly higher number.
