Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I got some questions on comparing value in different table.
Table A
Key | Value |
---|---|
1 | 10 |
2 | 20 |
3 | 30 |
Table B
Lower Range | Upper Range | Class |
---|---|---|
10 | 20 | Y |
20 | 30 | Y |
0 | 10 | N |
I would like to ask how to check which Key in table A belong to Class Y in Table B
Best Regards,
Louis
Hi,
TableA:
Mapping load * inline
[
Value,Class
10,Y
20,Y
30,Y
];
TableB:
load ApplyMap('TableA',Lower Range,'N') as Class,* inline
[
Loser Range,Upper Range
10,20
20,30
0,10
];
HTH
Sushil
Thx for reply!
The column Class does not exist in Table A.
Try this
TableA:
Mapping load 'Y' as Class,* inline
[
Value
10
20
30
];
Use an interval match in the pattern:
Let epsilon = 1e-9;
A:
LOAD Key,
Value
FROM ...table A...
Left Join (A)
IntervalMatch(Value)
LOAD [Lower Range],
[UpperRange] - epsilon as [Upper Range]
FROM ...table B...
Left Join (A)
LOAD [Lower Range],
[UpperRange] - epsilon as [Upper Range],
Class
FROM ...table B...
Replace the ellipses with the correct FROM clauses for your requirements. The value epsilon is a small amount to make sure that a value like 10 does not get assigned two class values.
You can drop the field [Lower Range] and [Upper Range] if you have no further need for them.