Lookup From Range

girish_talele — Wed, 07 Apr 2021 07:46:10 GMT

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.

Re: Lookup From Range

MayilVahanan — Wed, 07 Apr 2021 10:04:06 GMT

HI @girish_talele

Try with intervalmatch concept

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:

topic Re: Lookup From Range in QlikView

Lookup From Range

Re: Lookup From Range