Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

aboumejjane
Contributor

linking Excel 2010 sheet with mastercalender

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

1 Solution

Accepted Solutions
MVP
MVP

linking Excel 2010 sheet with mastercalender

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

2 Replies
MVP
MVP

linking Excel 2010 sheet with mastercalender

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

aboumejjane
Contributor

Re: linking Excel 2010 sheet with mastercalender

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

Community Browser