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: 
adarsh_a
Contributor III
Contributor III

Date formatting

Hi All,


In the excel file attached there are two date columns. Each column is having two date formats(spaces bfr date and without spaces).

I'm trying to convert into single format using Date# function but not able to achieve it in script(getting some values in date and some in number). 

Finally i need to extract Month,date and Year from this data.


Any help in resolving this is greatly appreciated. I have attached the excel file.


Regards,

Adarsh

2 Replies
johnw
Champion III
Champion III

Maybe something like this.

LOAD *
,date(monthstart([Create Date]),'MMM YYYY') as [Create Month]
,date(yearstart([Create Date]),'YYYY') as [Create Year]
;
LOAD
...
date(date#(subfield(trim([Create Date]),' ',1),'D/M/YYYY'),'D/M/YYYY') as [Create Date]
...
FROM somewhere
;

pathiqvd
Creator III
Creator III

Hi,

       If there different date formats in single column you can solve with alt function.Inside alt function just type whatever formats do you have and use date function you will get date format.

Table:

LOAD *,

  date(alt(date#(DT,'DD/MM/YYYY'),date#(DT,'DD-MM-YYYY'),date#(DT,'DD-MMM-YYYY')),'DD-MM-YYYY') as DT2 INLINE [

    DT

    13/10/2016

    14-10-2016

    15-Oct-2016

];

Regards,