Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
adarsh_a
New 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
MVP
MVP

Re: Date formatting

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

Re: Date formatting

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,