2 Replies Latest reply: Feb 25, 2013 3:12 PM by Nazeem Soeltan RSS

    Field association with datetime and master calendar.

      Dear Qlikview users,

       

      I have an excelsheet with temperatur and energy data. All energy and temperature values are registred every hour of a day. I want to make use of a Master Calendar to be able to filter the data on specific days and hours. However, when I import the data of the sheet, the associations between this data and the fields of the master calendar are not exactly how I want it.

      Before I go into the details, I want to give you an impression of the data and script.
      I've added it below.

       

      Excelsheet with data (click to expand):

      qv_data_ex.PNG


      Script for importing excelsheet:
      Cofely:
      LOAD timestamp#(Date(A) & ' ' & Time(A),'DD/MM/YYYY hh:mm:ss') as Cofely.DatumTijd, 
           B as Cofely.Dag, // Day
           C as Cofely.Uur, // Hour
           D as Cofely.BuitenTemperatuur, // Temperature
           G as Cofely.Elektrisch, // Energy
           H as Cofely.Gas, // Gas
           Date(A) as Cofely.Datum, // Date
           Time(A) as Cofely.Tijd  // Time
      FROM
      [C:\qlikview\dashboards\dataset.xlsx]
      (ooxml, explicit labels, header is 3 lines, table is Blad1);
      
      
      

       

       

      Mastercalendar snippet:

      QuartersMap:
      MAPPING LOAD 
      rowno() as Month,
      'Kw' & Ceil (rowno()/3) as Quarter
      AUTOGENERATE (12);
      
      
      Temp:
      Load
         Date(date#('01-01-2010','DD-MM-YYYY')) as minDate,
         Today() as maxDate
      Resident Cofely;
      
      
      Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
      DROP Table Temp;
      
      
      TempCalendar:
      LOAD
                $(varMinDate) + Iterno()-1 As Num,
                Date($(varMinDate) + IterNo() - 1) as TempDate
          AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
      
      
      MasterCalendar:
      Load
         TempDate AS Cofely.Datum,
         week(TempDate) As Calendar.Week,
         Year(TempDate) As Calendar.Jaar,
         Month(TempDate) As Calendar.Maand,
         Day(TempDate) As Calendar.Dag,
         YeartoDate(TempDate)*-1 as Calendar.HuidigYTDFlag,
         YeartoDate(TempDate,-1)*-1 as Calendar.LaatsteYTDFlag,
         inyear(TempDate, Monthstart($(varMaxDate)),-1) as Calendar.RC12,
         date(monthstart(TempDate), 'MMM-YYYY') as Calendar.MaandJaar,
         ApplyMap('QuartersMap', month(TempDate), Null()) as Calendar.Kwartaal,
         Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as Calendar.WeekJaar,
         if(weekday(TempDate) < 5, 'Werkdagen', 'Weekend') as Calendar.PeriodeType,
         WeekDay(TempDate) as Calendar.WeekDag
      Resident TempCalendar
      Order By TempDate ASC;
      Drop Table TempCalendar;
      
      

       

      As you can see in the excelsheet, on every row there is a datetime column which represents the date and every hour of that day.
      The master calendar is linked with the "Cofely.Datum"-column (which is only the date). The result of this is that there are multiple date-values.
      For example, the value "01-01-2010" occurs multiple times, because this datevalue exists for every hour.
      So when i select the first "01-01-2010" it is linked to "1:00" hour (24h format) . The second "01-01-2010" is linked to "2:00" hour etc. etc.

      I've added an example below.

       

      Association example;

      qv_ex.PNG

       

      I want to achieve the following:

      • When I select a date, all hours must be selected in it (1:00 - 24:00)

       

      Because, for my application I need to filter the data:

      • Per chosen day period
      • Per chosen time period

       

      How can I achieve this?

       

      I hope to find a suitable solution. When something is not clear, I will try to clarify it.