Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello, I have a qvd file with currency rates:
columns: Data | currency | rate
23.06.2024| EUR | 4,3123
LOAD
Data,
currency,
rate
FROM [lib://STOREPATH/ExchangeRates.qvd]
I would like to be able to extract the last exchange rate in the table (max) and use it later
If(ApplyMap('MaCurrency',case_id,'Brak')<>'PLN',commission * max depending on the currency . as New_commission
Hi @gz
Please let me know if this code helps you. I have some value changes but I think you can skip those. The main idea is to use the INNER JOIN with the Max Data by Currency.
Rate: LOAD Date(Date#(Data,'DD.MM.YYYY')) AS Data, currency, Num#(rate,'#,####')/10000 AS rate INLINE [ Data|currency|rate 23.06.2024|EUR|4,3123 22.06.2024|EUR|2,3123 19.06.2024|EUR|3,3123 28.06.2024|USD|2,3123 22.06.2024|USD|2,1122 21.06.2024|USD|3,5223 ](delimiter is '|') ; INNER JOIN (Rate) LatestRate: LOAD currency, Max(Data) AS Data RESIDENT Rate GROUP BY currency ; RateMap: Mapping Load currency, rate Resident Rate ; SampleData: LOAD TransactionId, Currency, Amount, ApplyMap('RateMap',Currency) AS RateApplied, ApplyMap('RateMap',Currency) * Amount AS AmountAdjusted INLINE [ TransactionId, Currency, Amount 12343, EUR, 1000 52343, EUR, 2000 67343, USD, 3000 ];
This is the sample result:
Regards,
Mark Costa
Hi @gz
I was typing the answer when I saw @marksouzacosta post below who provided you with the steps to achieve what you have described. One thing which initially was not clear to me was if you want to have "Max rate" or the "Latest rate" - if it is the latest then Marks answer clarifies the concept.
cheers