How to compare value in different table and without key

[Anonymous]

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

[sushil353]

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

[Anonymous]

Thx for reply!

The column Class does not exist in Table A.

[sushil353]

Try this

TableA:

Mapping load 'Y' as Class,* inline

[

Value

10

20

30

];

[jonathandienst]

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.