topic Re: date in QlikView

date

satishqlik — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have date field with diff date formats

and I've been trying to convert into one standard format.

But not working properly can any one suggest me on this?

LOAD [Call Id],

Status,

Odate,

Date((Alt(

Date#(Odate,'DD-MMM-YY'),

Date#(Odate,'YYY-MMM-D'),

Date#(Odate,'YYYY-MMM-DD'),

Date#(Odate,'MMM/DD/YYYY'),

Date#(Odate,'MMM/D/YYYY'),

Date#(Odate,'YYYY-D-MMM'),

Date#(Odate,'YYYY-DD-MMM'),

Date#(Odate,'YY-MMM-DD'),'DD-MMM-YY')))as mynewdate

FROM

<>;

Re: date

Chanty4u — Tue, 02 Aug 2016 09:32:34 GMT

try this

SET DateFormat='DD-MMM-YY';

LOAD [Call Id],

Status,

Odate,

Date(Date#(Odate,'YY-MMM-DD'),'DD-MMM-YY')))as mynewdate

Re: date

satishqlik — Tue, 02 Aug 2016 09:36:51 GMT

Yes tried but still not working!

Re: date

Chanty4u — Tue, 02 Aug 2016 09:39:54 GMT

den try below

Alt( Date#(Odate,'DD-MMM-YY'),

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

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

Date#(Odate,'YYYYMMDD'),

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

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

) as Odate

or else

date(floor(alt(

Date#(Odate,'DD-MMM-YY'),

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

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

Date#(Odate,'YYYYMMDD'),

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

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

)), 'DD/MM/YYYY') as Odate

Re: date

sunny_talwar — Tue, 02 Aug 2016 10:23:49 GMT

Try this:

Table:

LOAD [Call Id],

Status,

Odate,

Date(Alt(Date(Odate),

Date#(Odate,'DD-MMM-YY'),

Date#(Trim(Odate),'M/D/YYYY'),

Date#(Odate,'YYY-MMM-D'),

Date#(Odate,'YYYY-MMM-DD'),

Date#(Odate,'MMM/DD/YYYY'),

Date#(Odate,'MMM/D/YYYY'),

Date#(Odate,'YYYY-D-MMM'),

Date#(Odate,'YYYY-DD-MMM'),

Date#(Odate,'YY-MMM-DD')), 'DD-MMM-YY') as mynewdate

FROM

[..\..\..\Downloads\data.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: date

antoniotiman — Tue, 02 Aug 2016 10:33:43 GMT

I think You forgot

DD-MMM-YYYY format in Alt() expression