topic QlikView InnerJoin with Intervalmach creates duplicate records, XRate in QlikView

QlikView InnerJoin with Intervalmach creates duplicate records, XRate

Mon, 23 Sep 2013 08:30:04 GMT

Hello,

I'm facing a really strange situation. After using following syntax, only SOME of my records are being duplicated.

Situation: FactTable and TCURR (currency rates)

FactTable:

load *, CurrencyLink, CurrencyDate from FactTable.qvd (qvd);

TCURR:

     Type,
     CurrencyLink as CurrencyLink,
     toCurrency as _Currency,
     toCurrency as toCurrency,
     Date(fromDate, 'DD/MM/YYYY') as fromDate,
     Date(toDate, 'DD/MM/YYYY') as toDate,
     XRate
FROM $(vSource)TCURR_TCURF.qvd (qvd);

inner JOIN (TCURR)
INTERVALMATCH (CurrencyDate, CurrencyLink, Type)
LOAD fromDate, toDate, CurrencyLink, Type RESIDENT TCURR;

Result:

for the most of the records, just one XRate is found, which is correct.

For some of them, additional, same record in the table TCURR will be created and therefore XRate is counted twice so high.

Do you have any idea, why it is so, and how to prevent this behaviour? It could be, of course because of my inner join....

TCURR: Type D, CurrencyLink EUR, toCurrency CHF, fromDate 01.08.2013, toDate 31.12.9999, XRate 0,8

FactTable: Invoice 1, Sales 100, Currency EUR.

Thank you

Regards

Stan

Re: QlikView InnerJoin with Intervalmach creates duplicate records, XRate

Tue, 24 Sep 2013 09:26:16 GMT

I've prepared a small test scenario with a small amout of test data. If someone will have time, can look at it in the attachement.

I saw this post but it is not helping me with this issue:

http://community.qlik.com/thread/18106

Re: QlikView InnerJoin with Intervalmach creates duplicate records, XRate

Fri, 27 Sep 2013 11:57:35 GMT

Perhaps I found solution on my own.

If you should do a currency translation TCURR_TCURF from SAP as intervalmatch and inner join, be aware you use a LOAD DISTINCT ... RESIDENT TCURR

COPA:

load * from copa_tmp.qvd (qvd);
inner JOIN (TCURR)
INTERVALMATCH (CurrencyDate, CurrencyLink, Type)
LOAD DISTINCT fromDate, toDate, CurrencyLink, Type
RESIDENT TCURR;

This is my solution.

Stan