topic Re: Date formatting in QlikView

Date formatting

adarsh_a — Tue, 03 Jan 2017 23:52:21 GMT

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

Re: Date formatting

johnw — Wed, 04 Jan 2017 00:39:26 GMT

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
;

Re: Date formatting

pathiqvd — Wed, 04 Jan 2017 11:22:28 GMT

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 [

13/10/2016

14-10-2016

15-Oct-2016

];

Regards,