Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for
Did you mean:
Contributor III

## Date lookup of previous date

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

Labels (2)

• ### Scripting

MVP

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:
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:
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)
RESIDENT Dates;

// read the ExchangeRates table order by date; if Rate is null, get the Rate of the previous row (PEEK)
tmp: