topic Re: IntervalMatch Issue in QlikView

IntervalMatch Issue

Mon, 10 Sep 2012 05:32:58 GMT

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.

Re: IntervalMatch Issue

Mon, 10 Sep 2012 08:19:40 GMT

Hi,

please find the attached file, it may help you.

Thanks,

Niranjan M.

Re: IntervalMatch Issue

hic — Mon, 10 Sep 2012 08:28:16 GMT

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

LOAD INT_TYPE, FROM_BALANCE, TO_BALANCE, CCY, ACTUAL_RATE

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

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

Re: IntervalMatch Issue

Mon, 10 Sep 2012 10:49:56 GMT

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

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

Regards

Zain.

Re: IntervalMatch Issue

Mon, 10 Sep 2012 11:10:30 GMT

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

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

Re: IntervalMatch Issue

hic — Mon, 10 Sep 2012 11:24:04 GMT

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$]);

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;

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