Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

QlikView InnerJoin with Intervalmach creates duplicate records, XRate

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 *, CurrencyLinkCurrencyDate 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

1 Solution

Accepted Solutions
Not applicable

Re: QlikView InnerJoin with Intervalmach creates duplicate records, XRate

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

2 Replies
Not applicable

Re: QlikView InnerJoin with Intervalmach creates duplicate records, XRate

XRate wrong.JPG.jpg

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

Not applicable

Re: QlikView InnerJoin with Intervalmach creates duplicate records, XRate

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

Community Browser