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: 
qlikviewwizard
Master II
Master II

Handling of different date formats

Hi All,

I have data as shown below

   

Date FormatDateQuarterMonth
M/D/YYYY8/5/2013Q3Aug
M/D/YYYY10/7/2013Q4Oct
DD/MM/YYYY15/04/2013Q-
DD/MM/YYYY18/07/2013Q-
DD/MM/YYYY20/05/2013Q-
DD/MM/YYYY21/06/2013Q-
DD/MM/YYYY22/01/2013Q-
DD/MM/YYYY22/03/2013Q-
DD/MM/YYYY24/07/2013Q-
DD/MM/YYYY24/09/2013Q-
DD/MM/YYYY26/06/2013Q-
DD/MM/YYYY27/03/2013Q-
DD/MM/YYYY29/07/2013Q-
DD/MM/YYYY29/10/2013Q-

When I derive Quarter, it is showing Q for DD/MM/YYYY. How to bring all the data into uniform format and eliminate Q.

Thanks in advance.

1 Solution

Accepted Solutions
srchilukoori
Specialist
Specialist

Add the following statement to the default environment variables on the qvw.

SET DateFormat='DD/MM/YYYY';

View solution in original post

2 Replies
ziadm
Specialist
Specialist

Hi

use the Alt function to return the valid date

alt( date#( MyDate, 'YYYY/MM/DD' ),

date#( MyDate, 'MyDate MM/DD/YYYY' ),

date#( MyDate, 'MM/DD/YY' ),

'Not valid date' )

Load

LOAD ,

alt( date#( MyDate, 'YYYY/MM/DD' ),

date#( MyDate, 'MyDate MM/DD/YYYY' ),

date#( MyDate, 'MM/DD/YY' ),

'Not valid date' )  as MyNewDate

FROM DB

You may extend it to as many formats as you think you could be loading 'MM-DD-YYYY', YYYY-MM-DD'

If this works please tag as resolved

Regards

Ziad

srchilukoori
Specialist
Specialist

Add the following statement to the default environment variables on the qvw.

SET DateFormat='DD/MM/YYYY';