Date format issue

[apthansh]

PFA sample file and QVW file.

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

Thank you for your help.

[Oleg_Troyansky]

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!

[sunny_talwar · Accepted solution]

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;

[Anil_Babu_Samineni]

Change it to

Date(Num#()) Combination

[vishsaggi]

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

[apthansh]

Awesome.Thank you much.