Loading a field with YYYY/MM as a date

[Jdance]

Hi, I am very new to Qlikview and I cannot find the answer to this question.

I have a dataset I load which contains a field 'date1' which is in the format YYYY/MM (notice there is no day value) and I also have another field 'date2' with format DD/MM/YYYY. Both variables are recordings of dates but the first one does not include the day value.

My aim is to use a flag variable which marks 'Y' for every case where date1 > date 2 (date1 is later than date2).  date2 loads as a date so there is no problem there. The problem is that the original field 'date1' does not load as a date value, thus I cannot compare the 2 values. How can I import a field in this form and still use it as a date?

Thanks for reading and looking forward to your suggestions 🙂

Example of the fields:

date1	date2
2018/12	26/04/2018
2018/08	26/04/2018
2018/12	13/12/2018
2018/10	13/12/2018

[Vegar · Accepted solution]

Hi @Jdance and welcome into the QlikView community.

Try the logic below. Date#() forces window to evaluate the field as a date with given format.

Load
*,
If(date1 > date2 , 'Y') as Flag
;
Load
Date#(date1,'YYYY/MM' ) AS date1,
Date#(date2 ,'DD/MM/YYYY' ) AS date2
FROM ...

[Jdance]

Hahahaha I was doing the same as you I just forgot to include the ' ' around the YYYY/MM.

Thanks for the help!