Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
PFA sample file and QVW file.
Not sure what I am doing wrong in date format.Its not working.
Thank you for your help.
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;
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!
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;
Change it to
Date(Num#()) Combination
Try not to use the Regular keywords as your FieldNames. Like Date.
Awesome.Thank you much.