Date lookup of previous date

[-Steve-]

Hi,

We have an invoice table, which contains the date;

We have a table of Exchange Rates, which does not have records for every date;

How can I look up the previous day, last exchange rate record;

Invoice Date:

03/01/2022

Exchange Rates:

01/01/2022

02/01/2022

So in the above example I would want to do a "Lookup less than or equal to the Invoice Date" - so it returns the value again 02/01/2022.

Any help would be appreciated.

Thanks

[maxgro]

You can calculate the missing exchange rates in the script

One way is to fill the missing data in the exchange rate table
Here you can find a detailed explanation https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394

// test data

InvoiceDate:
Load InvDate, InvDate as ExcDate inline [
InvDate
01/01/2022
03/01/2022
];

ExchangeRates:
Load * inline [
ExcDate, Rate
25/12/2021, 200
01/01/2022, 100
02/01/2022, 101
01/03/2022, 105
];

// set the min and the max date of exchange rates
LET vMinExc = '01/12/2021';
LET vMaxExc = Date(Floor(YearEnd(Today())));

// Make a table with all dates between min and max (vMinExc vMaxExc)
Dates:
LOAD
Date('$(vMinExc)' + iterno()-1) as ExcDate
AutoGenerate 1
WHILE Date('$(vMinExc)' + iterno()-1) <= '$(vMaxExc)';

// join ExchangeRates and Dates; after this step ExchangeRate has all the dates between min and max
JOIN (ExchangeRates)
LOAD ExcDate
RESIDENT Dates;

// read the ExchangeRates table order by date; if Rate is null, get the Rate of the previous row (PEEK)
tmp:
NOCONCATENATE LOAD
ExcDate,
IF(ISNULL(Rate), PEEK('Rate'), Rate) as Rate
RESIDENT ExchangeRates
ORDER BY ExcDate;

// rename, drop
DROP TABLE ExchangeRates;
RENAME TABLE tmp TO ExchangeRates;
DROP TABLE Dates;