topic Re: Date format issue in QlikView

Date format issue

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

PFA sample file and QVW file.

Not sure what I am doing wrong in date format.Its not working.

Thank you for your help.

Re: Date format issue

Oleg_Troyansky — Tue, 30 May 2017 13:33:09 GMT

Hi,

the date seems to be coming from Excel in a form of a 5-digit number (the Date Serial Number), not as a formatted date.

The Crosstable load converts that number into a string value (still 5-digit). What you need to do is convert the string back into numeric format, and then apply date() to it. No need to use date#() because it's not a formatted date string as it looks like...

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming to Boston this October!

Re: Date format issue

sunny_talwar — Tue, 30 May 2017 13:34:26 GMT

Try this

RawSeats:

CrossTable (Date, Seats)

LOAD [Location Code],

[42278]

FROM

[Date Format.xlsx]

(ooxml, embedded labels, table is Committed);

Seats:

LOAD "Location Code" as Seats_LocationCode,

Date(Num#(Date), 'YYYY-MM-DD') as Date,

sum(Seats) as Seats

Resident RawSeats

group by "Location Code", Date(Num#(Date), 'YYYY-MM-DD');

DROP TABLE RawSeats;

Re: Date format issue

Anil_Babu_Samineni — Tue, 30 May 2017 13:37:38 GMT

Change it to

Date(Num#()) Combination

Re: Date format issue

vishsaggi — Tue, 30 May 2017 13:40:18 GMT

Try not to use the Regular keywords as your FieldNames. Like Date.

Re: Date format issue

apthansh — Tue, 30 May 2017 13:47:24 GMT

Awesome.Thank you much.