topic Re: date conversion in qlikview in Water Cooler

date conversion in qlikview

andrea0901 — Fri, 18 Jan 2019 04:57:57 GMT

Hi friends,

Please help me with the below scenario.

i have a currency date format as 10.01.2009

and document date format as 2018-05-22

as per the document date i need to fetch the currency dates in front end.

i do not want to change the date format , both the date formats should be as above but need to map and fetch both the data from those columns.

please kindly help.

Re: date conversion in qlikview

uacg0009 — Fri, 18 Jan 2019 05:56:30 GMT

Hi Andrea,
Do you mean you want to keep the format for those 2 columns in back-end?
If yes, I think you can use:
date(currency_date,'MM.DD.YYYY') as currency_date,
date(document_date,'YYYY-MM-DD') as document_date

If not, could you please clarify what you want?
Thanks.
Aiolos

Re: date conversion in qlikview

andrea0901 — Fri, 18 Jan 2019 09:32:46 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: date conversion in qlikview

uacg0009 — Mon, 21 Jan 2019 03:55:48 GMT

Hi,
According to your scenarios, could you please give a sample for the second one?
because for the first scenario, you can use left join to solve it, like:
left join
load currency_date as document_date,
exchange_rate as lookup
resident yourtable;

Aiolos