topic Re: linking Excel 2010 sheet with mastercalender in QlikView

linking Excel 2010 sheet with mastercalender

Anonymous — Mon, 12 Sep 2011 13:47:15 GMT

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

linking Excel 2010 sheet with mastercalender

swuehl — Mon, 12 Sep 2011 23:41:51 GMT

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

Re: linking Excel 2010 sheet with mastercalender

Anonymous — Tue, 13 Sep 2011 06:39:03 GMT

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