How to use applymap and getting the higest date?

[heij1421]

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?

[Frank_Hartmann]

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;

[Kushal_Chawda]

@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;

[heij1421]

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.

[Kushal_Chawda]

@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;

[heij1421]

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

[Kushal_Chawda · Accepted solution]

@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;