Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Mixed date format in a single field

I have date field in my xl file. Like 

Date:

DD.MM.YY
DD.MM.YYYY
DD-MM-YY
DD-MM--YYYY

I want  convert into single format like DD-MM-YY. please suggest me.

6 Replies
Highlighted
Specialist III
Specialist III

=date(date#(datefield,'DD/MM/YY'),'DD/MM/YY')

Channa
Highlighted

use alt().
see in help menu for details.

Regards,
Prashant Sangle
Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
Master II
Master II

assuming your date field's name is date_field and theya re real dates but not string try the below

DATE(date_field,'DD-MM-YY')
Learning never stops.
Highlighted
Master II
Master II

if they are strings try the below

alt(date(date#(purgechar(date_field,'.-')'DDMMYYYY'),'DD-MM-YY'),
date(date#(purgechar(date_field,'.-')'DDMMYY'),'DD-MM-YY')
)

 

 

Learning never stops.
Highlighted
Specialist III
Specialist III

Hi,

As suggested by @PrashantSangle you can try to use Alt function.

Syntax:

Date(Alt(Date#(Date, 'FirstFormat'), Date#(Date, 'SecondFormat'), 'RequiredFormat') as Date

 

Example:

Alt( Timestamp#(MixDate,'M/D/YYYY h:mm tt'),

Date#(MixDate,'M/D/YYYY'),

Date#(MixDate,'D/M/YYYY'),

Date#(MixDate,'YYYYMMDD'),

Date#(MixDate,'DD.MM.YYYY'),

Date#(MixDate,'YYYY-MM-DD')

) as MixDate

 

Thanks,

Mohammed Mukram

Highlighted
Master II
Master II

try this:

Alt( 
 Date(Date#(Date,'DD-MM--YYYY'),'DD-MM-YY'),
 Date(Date#(Date,'DD.MM.YY'),'DD-MM-YY'),
 Date(Date#(Date,'DD.MM.YYYY'),'DD-MM-YY'),
 Date(Date#(Date,'DD-MM-YY'),'DD-MM-YY') 
 ) as Date