Discussion Board for collaboration on QlikView Scripting.
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)
load *, CurrencyLink, CurrencyDate from FactTable.qvd (qvd);
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;
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.
Go to 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
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.
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: