Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

How to use applymap and getting the higest date?

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?

Labels (2)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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

View solution in original post

6 Replies
Highlighted
Master II
Master II

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;

 

Unbenannt.png

 

 

Highlighted
MVP
MVP

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

 

Highlighted
Partner
Partner

Thanks for the replies both @Frank_Hartmann and @Kush . 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 

heij1421_1-1601283824405.png

 

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.

 

Highlighted
MVP
MVP

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

 

 

Screenshot 2020-09-28 105003.png

Highlighted
Partner
Partner

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

Highlighted
MVP
MVP

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

View solution in original post