Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Jdance
Contributor II
Contributor II

Loading a field with YYYY/MM as a date

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:

date1date2
2018/1226/04/2018
2018/0826/04/2018
2018/1213/12/2018
2018/1013/12/2018
Labels (4)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

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 ...

View solution in original post

2 Replies
Vegar
MVP
MVP

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
Contributor II
Contributor II
Author

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

Thanks for the help!