Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Date format issue

PFA sample file and QVW file.

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

Thank you for your help.

1 Solution

Accepted Solutions
Highlighted

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;

View solution in original post

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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!

Oleg Troyansky

Masters Summit for Qlik is going virtual! Nov 18th - I'll teach Advanced Set Analysis and AGGR

Highlighted

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;

View solution in original post

Highlighted

Change it to

Date(Num#()) Combination

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Champion III
Champion III

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

Highlighted
Creator
Creator

Awesome.Thank you much.