topic Re: Applymap query in Water Cooler

Applymap query

andrea0901 — Sat, 19 Jan 2019 07:08:40 GMT

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.

Re: Applymap query

Gysbert_Wassenaar — Sat, 19 Jan 2019 10:03:26 GMT

See attached qvw

Re: Applymap query

andrea0901 — Sat, 19 Jan 2019 11:04:06 GMT

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 .

Re: Applymap query

Gysbert_Wassenaar — Sat, 19 Jan 2019 18:14:34 GMT

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.

Re: Applymap query

andrea0901 — Mon, 21 Jan 2019 04:48:45 GMT

Thank you so much Gysbert. Thanks for your help.