Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. 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';