7 Replies Latest reply: Jan 8, 2013 11:51 AM by Mathias Mühlfelder RSS

    MasterCalender question - duplicates

    Mathias Mühlfelder

      Folks,

       

      I am loading a Master Calender with this scirpt (copied from this forum and adjust slightly):

      Let varMinDate = 41276;
      Let varMaxDate = 41278;
      // DROP Table Temp;
      
      TempCalendar:
      LOAD
                     $(varMinDate) + Iterno()-1 As Num,
                     Date(($(varMinDate) + IterNo() - 1), 'DD.MM.YYYY') as TempDate
                     AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
      
      
      
      MasterCalendar:
      Load
                     TempDate AS MasterDaily,
                     hour(TempDate) As Hour,
                     Date(TempDate, 'DD.MM.YYYY') As FullDate,
                     week(TempDate) As Week,
                     Year(TempDate) As Year,
                     Month(TempDate) As Month,
                     Day(TempDate) As Day,
                     date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
                     Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
                     WeekDay(TempDate) as WeekDay
      Resident TempCalendar
      Order By TempDate ASC;
      // Drop Table TempCalendar;
      
      

       

      Now the question:

      As a result a get this table (using table box). Why do I get "duplicates" in the TempDate with the MasterDaily data.

      Shouldn't it be a signle line for each each date? I am loading the TempDate, and the TempDate itself looks fine, but by buildung the table i see duplicates.

       

      TempDate MasterDaily WeekDay
      02.01.2013 02.01.2013 Mi
      02.01.2013 03.01.2013 Do
      02.01.2013 04.01.2013 Fr
      03.01.2013 02.01.2013 Mi
      03.01.2013 03.01.2013 Do
      03.01.2013 04.01.2013 Fr
      04.01.2013 02.01.2013 Mi
      04.01.2013 03.01.2013 Do
      04.01.2013 04.01.2013 Fr

       

      Can somebody please comment and push me into the right direction,

       

      Thanks,

      Mathias

        • Re: MasterCalender question - duplicates
          Stefan Wühl

          I don't think you are getting duplicates here:

           

          Your two tables are not linked by a common field, so using fields from the two tables in one table box will lead to an implicite cross product of all records of the two tables. That's what you see here.

           

          Regards,

          Stefan

          • Re: MasterCalender question - duplicates
            Mathias Mühlfelder

            Thanks again swuehl,

             

            here is the real issue where I am coming from. I am using this script:

            Temp:
            Load
                           min(Date_Daily) as minDate,
                           max(Date_Daily) as maxDate
            Resident DAILY_DATA;
            
            Let varMinDate = Num(Floor(Peek('minDate', 0, 'Temp')));
            Let varMaxDate = Num(Floor(Peek('maxDate', 0, 'Temp')));
            Trace MIN varMinDate;
            Trace MAX varMaxDate;
            DROP Table Temp;
            
            TempCalendar:
            LOAD
                           $(varMinDate) + ((IterNo() - 1)/1440) As Num,
                           Date(($(varMinDate) + ((IterNo() - 1)/1440)), 'DD.MM.YYYY hh:mm') as TempDate
                           AutoGenerate 1 While ($(varMinDate) + ((IterNo() - 1)/1440)) < $(varMaxDate)+1;
            
            MasterCalendar:
            Load
                           TempDate AS MasterDaily,
                           Hour(TempDate) As MasterHour,
                           Date(Num, 'DD.MM.YYYY hh:mm') As MasterFullDateNum,
                           Date(TempDate, 'DD.MM.YYYY hh:mm') As MasterFullDate,
                           week(TempDate) As MasterWeek,
                           Year(TempDate) As MasterYear,
                           Month(TempDate) As MasterMonth,
                           Day(TempDate) As MasterDay,
                           Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
                           WeekDay(TempDate) as WeekDay
            Resident TempCalendar
            Order By TempDate ASC;
            Drop Table TempCalendar;
            
            

             

            The "real data" is coming from millions of lines out of a CSV file. The CSV contains a field named: MasterDaily.

            The data find together pretty much, but I get outputs like:

             

            What I do not understand is, why I am getting this duplicate lines e. g. for 00:21 or 00:24.

            I checked the raw data, and i got data in my CSV for 00:21 and 00:24.

             

            But using this data in diagrams shows the ' - ' NULL value for some dimensions.

             

            Do you have any idea on this as well? I thought I drill it down by myself, but I was on the wrong way with my tries around TempDate.

             

            Thanks,

            Mathias