Applymap query

[andrea0901]

Hi,

Actually below is my requirement:

Currency date	Exchange rate	Document Date	Lookup
11.01.2019	4.20870	12.01.2016	#N/A
10.01.2019	4.23295	12.02.2016	#N/A
09.01.2019	4.23695	11.01.2019	4.2087
08.01.2019	4.21035	10.01.2019	4.23295
07.01.2019	4.20135	09.01.2019	4.23695
04.01.2019	4.20320	08.01.2019	4.21035
03.01.2019	4.18665	07.01.2019	4.20135
02.01.2019	4.17580	04.01.2019	4.2032
28.12.2018	4.20705	03.01.2019	4.18665
27.12.2018	4.20925	02.01.2019	4.1758
26.12.2018	4.19595	28.12.2018	4.20705
21.12.2018	4.18850	27.12.2018	4.20925
20.12.2018	4.19015	26.12.2018	4.19595
19.12.2018	4.21000	21.12.2018	4.1885
18.12.2018	4.18650	20.12.2018	4.19015
17.12.2018	4.17800	19.12.2018	4.21
14.12.2018	4.16575	18.12.2018	4.1865
13.12.2018	4.14605	17.12.2018	4.178
12.12.2018	4.17585	14.12.2018	4.16575
11.12.2018	4.16810	13.12.2018	4.14605
10.12.2018	4.17800	12.12.2018	4.17585
07.12.2018	4.19585	11.12.2018	4.1681

first A, B, C columns i have pulled. 

i have to derive the 4th column (i.e Lookup)

First i need to search whether the document date is present in Currency date, if the exact date is there then i have to derive that date's exchange rate column. 

Second, if the document date is not equal to currency date, i need to take the next highest date based on the document date and derive that date's exchange rate column. 

could u please help in qlikview how to derive the whole scenario.

[Gysbert_Wassenaar · Accepted solution]

See attached qvw

[andrea0901 · Accepted solution]

Thank you for your reply Gysbert. I understood the logic what you have written.

Just one clarification.

If document date is not equal to currency date, then for that particular document date exchange rate should be next highest currency date exchange rate. 

so is this logic applied in that code. Please clarify .

[Gysbert_Wassenaar]

Yes, the logic for that is included. First the exchange rates are created for the missing dates. Once all the dates exists and have an exchange rate any document date can be looked up. Well, except those from 2016 for which no exchange rates were available in the data.

[andrea0901]

Thank you so much Gysbert. Thanks for your help.