QlikView InnerJoin with Intervalmach creates duplicate records, XRate

[#1]

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

[#2]

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

[#3 · Accepted solution]

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