IntervalMatch Issue

[#1]

I am attaching Application alongwith my data sources in excel files (containing three sheets , first one is about the rate definition , second one is about acutal data from client , and in the third sheet desired result is needed)

Currently using IntervalMatch but won't give my desired result.

Regards

Zain.

[#2]

Hi,

please find the attached file, it may help you.

Thanks,

Niranjan M.

[hic]

It is possible to have multiple keys in the intervalmatch. Here is an alternative solution:

A:

LOAD INT_TYPE, FROM_BALANCE, TO_BALANCE, CCY, ACTUAL_RATE

FROM Rate_Matrix2.xls (biff, embedded labels, table is [Rate Slab$]);

B:

LOAD ClientNo, INT_TYPE, CCY, Balance

FROM Rate_Matrix2.xls (biff, embedded labels, table is [Client Data$]);

Intervalmatch:

Left Join (B)

IntervalMatch (Balance,INT_TYPE,CCY) LOAD  FROM_BALANCE,TO_BALANCE,INT_TYPE,CCY

Resident A;

HIC

[#4]

Thanks for the solution

But , if you observe the output table, you will notice that ClientNo: 2,6,9,10,15 have repeating records just because

these clients have exactly same Balances as (To_balance or From_bal have)

(i.e:  ClientNo  2 have Balance 4999999 on the other hand in Rate Slab table

INT_TYPE =' ISI'  has Ranges from -0.1  -    4999999

  and                       4999999 -    9999999

So, that's why client 2 falls on both ranges

My Question is:

IF MatchInterval works like Between operator so, logically Between Operator follows (>=  <=)  Rule.

But Is there any possible way that can implement this  (>  <=) rule

OR

How do i get rid off with such (i.e: Client No 2, or you can say repeating records) kind of problem

Regards

Zain.

[#5]

Thanks for the solution

but , if you observe the output table, you will notice that

ClientNo: 2,6,910,15 have repeating record just because

these clients have exactly same Balances as (To_balance or From_bal  have)

(i.e:  ClientNo  2 have Balance 4999999

on the other hand in Rate Slab table

INT_TYPE =' ISS'  has Ranges from -0.1-4999999

               and        4999999-9999999

so that's why client 2 falls on both ranges

My Question

is IF MatchInterval works like Between operator so, logically

Between Operator follows (>=  <=)

But i need (>  <=) rule

or

How do i get rid off with such(Client No 2, or you can say repeating records) kind of problem

[hic · Accepted solution]

Intervalmatch is defined as (>= <=) and this cannot be changed. The balance 4999999 belongs to two intervals simultaneously. But you can define your intervals differently:

Temp_A:

LOAD INT_TYPE, TO_BALANCE, CCY, ACTUAL_RATE

FROM Rate_Matrix2.xls (biff, embedded labels, table is [Rate Slab$]);

A:

Load *,

          if(CCY=peek(CCY) and INT_TYPE=Peek(INT_TYPE),RangeSum(Peek(TO_BALANCE),0.0001),-1) as FROM_BALANCE

          resident Temp_A

          order by CCY, INT_TYPE, TO_BALANCE;

Drop Table Temp_A;

B:

LOAD ClientNo, INT_TYPE, CCY, Balance

FROM Rate_Matrix2.xls (biff, embedded labels, table is [Client Data$]);

Intervalmatch:

Left Join (B)

IntervalMatch (Balance,INT_TYPE,CCY) LOAD  FROM_BALANCE,TO_BALANCE,INT_TYPE,CCY

Resident A;

HIC