Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi,
Thank you for taking the time to resolve, yes, there was an issue with the data model which was resolved
Thank you
Daniel