Double data returned with multiple currency rates

[davyqliks]

I have an issue where, if a second currency is added for second month, figures and rates for the initial month are doubled, skewed when manually calculating but correct in the SUM().

I currently lookup to a translation table for currency conversions.
Here is the load code
//Load the currency fields as lookupgbp\\

Map_Currency_Rate1:
Mapping Load
[Base Currency] & '|' & Date([Voucher Date],'DD/MM/YYYY') AS CurrencyKey1,
[FX rate Base to GBP]
FROM
[\\FILE01\Support Files\QVFiles\Group Sales Report\Mapping\Qlikview mapping_V6 (updated Item type mapping).xlsx]
(ooxml, embedded labels, table is FXMAPtoGBP);

//Load the currency fields as lookup hkd\\

Map_Currency_Rate2:
Mapping Load
[Base Currency] & '|' & Date([Voucher Date],'DD/MM/YYYY') AS CurrencyKey2,
[FX rate Base to HKD]
FROM
[\\FILE01\Support Files\QVFiles\Group Sales Report\Mapping\Qlikview mapping_V6 (updated Item type mapping).xlsx]
(ooxml, embedded labels, table is FXMAPtoHKD);

LOAD [Base Currency],
//[Voucher Date],
[FX rate Base to GBP]
FROM
[\\FILE01\Support Files\QVFiles\Group Sales Report\Mapping\Qlikview mapping_V6 (updated Item type mapping).xlsx]
(ooxml, embedded labels, table is FXMAPtoGBP);

LOAD [Base Currency],
//[Voucher Date],
[FX rate Base to HKD]
FROM
[\\FILE01\Support Files\QVFiles\Group Sales Report\Mapping\Qlikview mapping_V6 (updated Item type mapping).xlsx]
(ooxml, embedded labels, table is FXMAPtoHKD);

When I load a single month of currency for all location I get a correct return in the SUM and also manual calculation. Here is the format of using INR as an example
INR 01/01/2019 0.1122
INR 02/01/2019 0.1122
INR 03/01/2019 0.1122
INR 04/01/2019 0.1122
INR 05/01/2019 0.1122
INR 06/01/2019 0.1122
INR 07/01/2019 0.1122
INR 08/01/2019 0.1122
INR 09/01/2019 0.1122
INR 10/01/2019 0.1122
INR 11/01/2019 0.1122
INR 12/01/2019 0.1122
INR 13/01/2019 0.1122
INR 14/01/2019 0.1122
INR 15/01/2019 0.1122
INR 16/01/2019 0.1122
INR 17/01/2019 0.1122
INR 18/01/2019 0.1122
INR 19/01/2019 0.1122
INR 20/01/2019 0.1122
INR 21/01/2019 0.1122
INR 22/01/2019 0.1122
INR 23/01/2019 0.1122
INR 24/01/2019 0.1122
INR 25/01/2019 0.1122
INR 26/01/2019 0.1122
INR 27/01/2019 0.1122
INR 28/01/2019 0.1122
INR 29/01/2019 0.1122
INR 30/01/2019 0.1122
INR 31/01/2019 0.1122

Feb
INR 01/02/2019 0.1220
When I make a repeat of the above for the days of Feb with a different rate and filter on Voucher date in the report, I am seeing both the rate for Jan and Feb and there for having duplicate lines of data and the FX RATE in the Table is – null. When I remove the ratees for FEB INR. The data is perfect.

Has anyone experienced anything like this and do you have any advice please?
Thank you
Daniel

[Brett_Bleess · Accepted solution]

Daniel,

I am pretty certain this has to do with your data model, but folks are not likely responding as without a sample app to review things fully, they are likely hesitant to reply here, so if you are still struggling with things, if you can attach a reduced version etc. such that folks can see the full data model and your script and expressions, they may be able to come up with something at that point.  I am not a great developer, so I cannot really pose any good ideas here, but I would be willing to bet it likely has to do with the data model based upon what you have described, and with even a reduced model, folks would be able to see what your table structure looks like to know if that is the problem or not...  Sorry I do not have anything better here, but if you can attach something, I am betting someone will have a look and give you some feedback at that point.

Regards,
Brett

[davyqliks]

Hi,

Thank you for taking the time to resolve, yes, there was an issue with the data model which was resolved

Thank you

Daniel