2 Replies Latest reply: Sep 13, 2011 2:39 AM by Aissam Boumejjane RSS

    linking Excel 2010 sheet with mastercalender

    Aissam Boumejjane

      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