Looking up value in a range on another table

[aetingu12]

Dear all,

I am trying to map a calculated expression to a table.

My table is:

LOAD * INLINE[
CUSTOMER,BALANCE
A,150
B,200
C,350
D,400];

My reference table is:

LOAD * INLINE [MIN,MAX,RATE
100,200,QW
200,300,QE
300,400,QR
400,500,QT];

So basically if the expression sum(BALANCE) is between 100 and 200, I want the expression to print out QW.

ie.

CUSTOMER    BALANCE

A                         QW

B.                       QW

C.                       QR

D                        QT

Any help would be most appreciated.

Regards,

Aksel

[mdmukramali · Accepted solution]

Hi,

You can use Interval Match concept to achieve your result.

Something like below :

//Script:

LOAD * INLINE[
CUSTOMER,BALANCE
A,150
B,200
C,350
D,400];

Range:
LOAD * INLINE [MIN,MAX,RATE
100,200,QW
201,300,QE
301,400,QR
401,500,QT
]
;

Inner Join(Cust)
IntervalMatch(BALANCE)
LOAD MIN,MAX Resident Range;

Thanks,

Mohammed Mukram

[aetingu12]

Thank you.

Is there a way to do the same within an expression?

[NZFei]

Dimension is Customer.

Expression is:

ONLY(if(BALANCE>MIN and BALANCE<=MAX, RATE))

Result: