Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
Hope you can help me out. I have a excel 2010 sheets with departure dates. I have linked it to my Qlikview Calander, however I don't see the month's.
There's just a ' - ' instead of the month names. I tried the following:
The Departure date is has the following format: 'DD-MM-YYYY hh:mm:ss', but that should not be a problem right?
DATA:
LOAD
Location,
Destination,
Date(Departure) as Date,
Date(Departure) as DateFromExcel,
Month(Departure) as MonthFromExcel,
if(Destination='ABC',
if(Interval(Date(Delivery) - Date(Departure), 'D')<=8, 'OnTime', 'Late'),
if(Destination='XYZ' or Destination='CBA',
if(interval(Date(Delivery) - Date(Departure), 'D')<=9, 'OnTime', 'Late'))) as Status,
Departure,
Delivery
FROM
delete.xlsx
(ooxml, embedded labels, table is RAWDATA);
Let varMin = Num('01-01-2011');
Let varMax = Num('31-12-2011');
Let vWorkdays = NetWorkDays(varMin,varMax);
Date:
Load Date($(varMin) +RowNo () -1) as tempdate
AutoGenerate $(varMax) - $(varMin) +1;
Calender:
Load
Date(tempdate) as Date,
Year(tempdate) as Year,
'Q' & Ceil(Month(tempdate)/3) as Quarter,
Month(tempdate) as Month,
Week(tempdate) as Week,
DayName(tempdate) as DayName
Resident Date;
Drop Table Date;
Hope you can help!
Cheers
iSam
Hi,
aboumejjane wrote:
The Departure date is has the following format: 'DD-MM-YYYY hh:mm:ss', but that should not be a problem right?
Well, I think it is. Your departure is still a timestamp, not a date type, even if you formatted is with only the date part. So you have the data in your table, but QV will not successfully link your timestamp to the calendar date.
I think you should use
Date(daystart(Departure)) as Date,
in your excel load.
Regards,
Stefan
Hi,
aboumejjane wrote:
The Departure date is has the following format: 'DD-MM-YYYY hh:mm:ss', but that should not be a problem right?
Well, I think it is. Your departure is still a timestamp, not a date type, even if you formatted is with only the date part. So you have the data in your table, but QV will not successfully link your timestamp to the calendar date.
I think you should use
Date(daystart(Departure)) as Date,
in your excel load.
Regards,
Stefan
THANKS A LOT STEFAN!!!! That was indeed the problem!!