Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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!