6 Replies Latest reply: Jun 12, 2014 1:14 AM by vikas mahajan RSS

    Need help implementing Master Calendar


      I've just about exhausted all alternatives for implemnting master calendar and could really use some help. I've read all of the discussions very carefully and my results are still not correct. Can someone help me out? 

       

      When I create list boxes for the various date fields, there is no resulting lists. For example for months I only get a single month, and if I select it it doesnt highlight the appropriate data.

       

      Here is my load script:

      [event_fact]:

      LOAD user_sk,
          
      date_day_sk,
          
      Date#(date_day_sk,'YYYYMMDD') as [Period Date]
      FROM
      event_fact.csv

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq);


      I'm creating the [Period Date] field from the date_day_sk which is a surrogate key representing a date but it is a string value. String would be '20140514' which is 'YYYYDDMM' format

      Here is the Master Calendar Code:

       

      Temp_Calendar_Range:

      LOAD
       
      min(num([Period Date])) as MinDate,
       
      max(num([Period Date])) as MaxDate
      RESIDENT [event_fact];

      Let vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range');
      Let vMaxDate = Peek('MinDate', 0, 'Temp_Calendar_Range');

      DROP Table Temp_Calendar_Range;

      [Master Calendar]:

      LOAD DISTINCT
      Year(Temp_Date) * 100 + Month(Temp_Date) as [Period Date],
      Year(Temp_Date) as [Year],
      Month(Temp_Date) as [Month],
      Date(Temp_Date, 'YYYY-MM') as [Year-Month],
      'Q' &
      Ceil(Month(Temp_Date) /3) as [Quarter]
      ;
      LOAD DISTINCT
      $(vMinDate) + Iterno()-1 As Temp_Date 
      AUTOGENERATE(1)
      WHILE $(vMinDate) + IterNo()-1 <= $(vMaxDate)
      Let vMinDate = null;

      Let vMaxDate = null;