max rate from Exchange Rates table

[gz]

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

[marksouzacosta]

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

[Lech_Miszkiewicz]

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