Lookup From Range

[girish_talele]

Hi All,

As shown in the below image there are two sets of data.

"Data 1" contains Category wise Wt wise Rate. e.g Cat "AAA" Wt from 200 to 249 -> Rate will be 10 & so on.

"Data 2" contains Category & Wt Value. The result is expected as shown in "Result Table" i.e. The Rate of respective Category & Wt Range should be looked-up.

Using flowing script it is achieved, but wondering if there can be a better way to achieve the result as the data in both tables 1 &2 is huge.

//===>

Data_1:
LOAD * INLINE [
CtType, Wt_From, Wt_To, RATE
AAA, 200, 249, 10
AAA, 250, 299, 20
AAA, 300, 350, 30
BBB, 200, 249, 11
BBB, 250, 299, 22
BBB, 300, 350, 33
];
//===================================================
Data_2:
LOAD * INLINE [
Ct_Cat, Ct_Wt
AAA, 200
AAA, 230
AAA, 249
AAA, 250
AAA, 350
BBB, 260
BBB, 310
];
//===================================================
Left Join(Data_2)
Data_1_1:
Load
CtType                       AS Ct_Cat,
Wt_From                  AS Ct_Wt_From,
Wt_To                       AS Ct_Wt_To,
RATE                          AS Ct_RATE
Resident Data_1;
//===================================================
Data_3:
NoConcatenate Load
*
Resident Data_2
Where Ct_Wt >= Ct_Wt_From AND Ct_Wt <= Ct_Wt_To;
Drop Table Data_2;
//====================================================

Thanks in Advance,

Girish.

[MayilVahanan]

HI @girish_talele 

Try with intervalmatch concept

Data_1:
LOAD * INLINE [
CtType, Wt_From, Wt_To, RATE
AAA, 200, 249, 10
AAA, 250, 299, 20
AAA, 300, 350, 30
BBB, 200, 249, 11
BBB, 250, 299, 22
BBB, 300, 350, 33
];
//===================================================
Data_2:
LOAD * INLINE [
CtType, Ct_Wt
AAA, 200
AAA, 230
AAA, 249
AAA, 250
AAA, 350
BBB, 260
BBB, 310
];

IntervalMatch(Ct_Wt, CtType)
LOAD Wt_From, Wt_To, CtType Resident Data_1;

Left Join
Load * Resident Data_1;

Left Join
LOAD * Resident Data_2;

DROP Table Data_1, Data_2;

O/P: