2 Replies Latest reply: Aug 28, 2012 2:21 PM by Rob Wunderlich RSS

    Link between date and interval

      Hi,

       

      I'm trying to make a link between 2 tables and I don't know how to do that.

      The problem is that I have a pair of fields in a table like datefrom and dateto. These 2 dates makes an interval.

      I want to link another date from a master calendar this way:

      A date from the master calendar will be linked with a register of the other table if the date from the master calendar is in the interval of the other table.

       

      Here is the relevant part of the script

      where

      REGFACTDateFrom,
      REGFACTDateTo,

      are the interval and

      Calendar_Date

      is the other date

       

       

      Labor_Rate:
      LOAD
      REGFACTDateFrom,
      REGFACTDateTo,
      REGFACTRegion AS Link_Data_SuperRegion,
      REGFACTType,
      REGFACTFactor;

      SQL SELECT *
      FROM `service_dashboard`.regionfactors;

       

      Con calendario:

       

      LET vDateMin = Num(MakeDate(2010,11,01));
      LET vDateMax = Num(date(Today()));

      TempCalendar:
      LOAD
      Date($(vDateMin) + RowNo() - 1) AS TempDate
      AUTOGENERATE 1
      WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

      MasterCalendar:
      LOAD
      Month (Date (monthstart(TempDate, 0),'DD/MM/YYYY')) AS Month,       
      Year (Date (yearstart (TempDate, 1, 11),'DD/MM/YYYY'))  AS Year,  
      Num(Month(TempDate)) AS MonthNumber,

      'FY'&
      Mid(Ceil(Year(AddMonths(TempDate,2))),3,2)&'M'&if(Len(Ceil(Month(AddMonths(TempDate,2))))=1,'0')&Ceil(Month(AddMonths(TempDate,2))) AS FiscalMonth,
      'FY'&
      Mid(Ceil(Year(AddMonths(TempDate,1))),3,2)&'M'&if(Len(Ceil(Month(AddMonths(TempDate,1))))=1,'0')&Ceil(Month(AddMonths(TempDate,1))) AS PreviousFiscalMonth,
      //'FY'&Mid(Ceil(Year(AddMonths(TempDate,0))),3,2)&'M'&if(Len(Ceil(Month(AddMonths(TempDate,0))))=1,'0')&Ceil(Month(AddMonths(TempDate,0))) AS Previous2FiscalMonth,   
             'FY'&Mid(Ceil(Year(AddMonths(TempDate,2))),3,2)&'Q'&Ceil(Month(AddMonths(TempDate,2))/3) AS FiscalQuarter,
      'FY'&
      Mid(Ceil(Year(AddMonths(TempDate,1))),3,2)&'Q'&Ceil(Month(AddMonths(TempDate,1))/3) AS PreviousFiscalQuarter,

      'Q'&
      Ceil(Month(AddMonths(TempDate,2))/3) AS Quarter,
      TempDate AS  Calendar_Date


      RESIDENT TempCalendar ORDER BY TempDate ASC;

      DROP TABLE TempCalendar;

      store MasterCalendar into C:\Users\casellag\Gerard\Customer Contribution Margin Report\Qlikview CCMR\Gerard - QV CCMR\CCMR_QVD_DATE.qvd;

      drop Table MasterCalendar;

       

       

      Thanks in advance.