topic How to use applymap and getting the higest date? in App Development

How to use applymap and getting the higest date?

heij1421 — Sat, 16 Nov 2024 18:05:45 GMT

Hi,

I have a simple exchange rate table with exchange rates per Year - Month:
Exchangerate:
ExchangeRateDate, ExchangeRate
2020-01, 0.88
2020-02, 0.89

I have a mapping load function like this:

Map_Currency_Rate_PL:
Mapping Load
DATE(MonthStart(ExchangeRateDate),'YYYY-MM') AS CurrencyKey,
ExchangeRatePL
RESIDENT Exchangerate;

I also have a facts table with amounts, like
LineNo, transactiondate, amount
1, 2020-01-01, 100
2, 2020-03-30, 200

I the load of this fact table I use function:
APPLYMAP('Map_Currency_Rate_PL', DATE(MonthStart(transactiondate),'YYYY-MM'))

This Applymap function works fine for line 1, as there is an exchange rate for January 2020.
But for line 2 it returns nothing as it can not find an exchange rate in March 2020.

In this scenario I want to get the higest date from the exchange rate table which is February 2020 in case the date is not available.
How do I do that?

Re: How to use applymap and getting the higest date?

Frank_Hartmann — Thu, 17 Sep 2020 18:25:13 GMT

I guess Applymap wont work in your case. what you could do is to join the 2 Tables by Date and then using peek function to get the wanted value.

e.g.

Fact: Load *, left(transactiondate,7) as ExchangeRateDate; Load * Inline [ LineNo, transactiondate, amount 1, 2020-01-01, 100 2, 2020-03-30, 200 ]; join Load * Inline [ ExchangeRateDate, ExchangeRate 2020-01, 0.88 2020-02, 0.89 ]; NoConcatenate Load *,if(isnull(ExchangeRate),peek(ExchangeRate),ExchangeRate) as ExchangeRate_New Resident Fact Order by ExchangeRateDate; drop table Fact;

Re: How to use applymap and getting the higest date?

Kushal_Chawda — Thu, 17 Sep 2020 22:13:28 GMT

@heij1421 another approach with applymap itself. Assuming transactiondate has a proper date format. If not then first convert it to date format using Date# function

Exchangerate: mapping LOAD date(date#(ExchangeRateDate,'YYYY-MM'),'YYYY-MM') as ExchangeRateDate, ExchangeRate FROM ExchangeRateTable; Exchangerate_latest: load FirstSortedValue(ExchangeRate,-ExchangeRateDate) as Latest_Exchange_Rate; LOAD date(date#(ExchangeRateDate,'YYYY-MM'),'YYYY-MM') as ExchangeRateDate, ExchangeRate FROM ExchangeRateTable; let vLatest_Exchange_Rate = Peek('Latest_Exchange_Rate',0,'Exchangerate_latest'); DROP Table Exchangerate_latest; Fact: Load LineNo, transactiondate, amount, ApplyMap('Exchangerate',date(transactiondate,'YYYY-MM'),$(vLatest_Exchange_Rate)) as FROM transaction table;

Re: How to use applymap and getting the higest date?

heij1421 — Mon, 28 Sep 2020 09:04:04 GMT

Thanks for the replies both @Frank_Hartmann and @Kushal_Chawda . Both solutions are OK with the given data, but I have to admin my request was not complete. Sorry for that. It should not give the exchange rate for the higest date, but the exchange rate for the higest date before the transaction date.

Let me explane:

When I use this script from Frank Hartman:

Fact:
Load *, left(transactiondate,7) as ExchangeRateDate;
Load * Inline [
LineNo, transactiondate, amount
1, 2020-01-01, 100
2, 2020-02-12, 100
3, 2020-02-13, 100
4, 2020-04-01, 100
5, 2020-05-10, 100
6, 2020-06-01, 100
];

join

[ExchangeRatetable]:
Load * Inline [
ExchangeRateDate, ExchangeRate
2020-01, 0.81
2020-02, 0.82
2020-03, 0.83
2020-05, 0.85
];

NoConcatenate

Load LineNo,
transactiondate,
amount ,
if(isnull(ExchangeRate),peek(ExchangeRate),ExchangeRate) as ExchangeRate_New
Resident Fact Order by ExchangeRateDate;
drop table Fact;

exit Script;

It returns

Line No 1, 2, 3 and 5 are OK.

Line 4 should return an exchange rate of 0,83 as in April the last known exchange rate before April was from March
Line 6 should return an exchange rate of 0,85 as in June the last know exchange rate was from May.
So if there is no exchange rate available for the transaction date it should return the last exchange rate before that year-month.

Re: How to use applymap and getting the higest date?

Kushal_Chawda — Mon, 28 Sep 2020 09:51:55 GMT

@heij1421 try below. Assuming that your transaction date is in proper date format and not text. Also format of the exchange date rate is YYYY-MM

Exchange_Rate_Map: mapping load monthstart((date#(ExchangeRateDate,'YYYY-MM'))) as ExchangeRateDate, ExchangeRate; Load * Inline [ ExchangeRateDate, ExchangeRate 2020-01, 0.81 2020-02, 0.82 2020-03, 0.83 2020-05, 0.85 ]; Fact: Load *,applymap('Exchange_Rate_Map',monthstart(transactiondate),null()) as ExchangeRate Inline [ LineNo, transactiondate, amount 1, 2020-01-01, 100 2, 2020-02-12, 100 3, 2020-02-13, 100 4, 2020-04-01, 100 5, 2020-05-10, 100 6, 2020-06-01, 100 ]; Final: NoConcatenate Load LineNo, transactiondate, amount, if(IsNull(ExchangeRate),Peek(ExchangeRate),ExchangeRate) as ExchangeRate Resident Fact Order by transactiondate; Drop Table Fact;

Re: How to use applymap and getting the higest date?

heij1421 — Mon, 28 Sep 2020 10:11:56 GMT

Hi @Kushal_Chawda . In your solution line 4 returns 0.82 where it should return 0.83

Re: How to use applymap and getting the higest date?

Kushal_Chawda — Mon, 28 Sep 2020 10:33:48 GMT

@heij1421 try below

Fact: Load *, monthstart(transactiondate) as ExchangeRateDate, 1 as Flag Inline [ LineNo, transactiondate, amount 1, 2020-01-01, 100 2, 2020-02-12, 100 3, 2020-02-13, 100 4, 2020-04-01, 100 5, 2020-05-10, 100 6, 2020-06-01, 100 ]; Join(Fact) load monthstart(date(date#(ExchangeRateDate,'YYYY-MM'))) as ExchangeRateDate, ExchangeRate; Load * Inline [ ExchangeRateDate, ExchangeRate 2020-01, 0.81 2020-02, 0.82 2020-03, 0.83 2020-05, 0.85 ]; T1: NoConcatenate Load LineNo, transactiondate, amount, Flag, if(IsNull(ExchangeRate),Peek(ExchangeRate),ExchangeRate) as ExchangeRate Resident Fact Order by ExchangeRateDate,transactiondate; DROP Table Fact; Final: NoConcatenate Load LineNo, transactiondate, amount, Flag, ExchangeRate Resident T1 Where Flag=1; DROP Table T1;