linking Excel 2010 sheet with mastercalender

[Anonymous]

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

[swuehl · Accepted solution]

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

[Anonymous]

THANKS A LOT STEFAN!!!! That was indeed the problem!!