Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
charfedine
New Contributor

Date transaformation

Hi

in the process of loading my data, i found out that date column was in this format " Thu Jul 09 12:33:44 WAT 2009 " and when i tried to use the function Day on that format it didn't work !

So i was wondering if there's a format transformation function that could help me or any other solution !

2 Replies
sasiparupudi1
Honored Contributor III

Re: Date transaformation

Try like this

Date(Date#(SubField(Replace(Date22,'WAT ',''),' ',-1)&'-'&SubField(Replace(Date22,'WAT ',''),' ',2)&'-'&SubField(Replace(Date22,'WAT ',''),' ',3),'YYYY-MMMM-DD')) As Date23

Load

Replace(Date22,'WAT ','') as Date22,

Date(Date#(SubField(Replace(Date22,'WAT ',''),' ',-1)&'-'&SubField(Replace(Date22,'WAT ',''),' ',2)&'-'&SubField(Replace(Date22,'WAT ',''),' ',3),'YYYY-MMMM-DD')) As Date23

  Inline

[Date22

Thu Jul 09 12:33:44 WAT 2009

];

thiago_justen
Valued Contributor III

Re: Date transaformation

Another option would be this:

Table:

load

*,

    MakeDate(Year,Month,Day) as Date_Formatted;

load

     Date#(SubField(replace(date_string,' ',','),',',1),'WWW') as DayName,

     Month(Date#(SubField(replace(date_string,' ',','),',',2),'MMM')) as Month,

     Day(Date#(SubField(replace(date_string,' ',','),',',3),'DD')) as Day,

     SubField(replace(date_string,' ',','),',',4) as Time,

     Year(Date#(SubField(replace(date_string,' ',','),',',6),'YYYY')) as Year;

load * Inline [

date_string

" Ter Jul 09 12:33:44 WAT 2009 "

];

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago