2 Replies Latest reply: Sep 9, 2015 2:48 PM by Joanna Seldon RSS

    calendar - synthetic key issue

    Joanna Seldon

      HI

       

      I derived a calendar as follows

       

      Calendar: DECLARE FIELD DEFINITION TAGGED '$date'
      Parameters first_month_of_year = 1,
      fiscal_first_month_of_year = 4

      Fields

          Year($1) As Year Tagged '$year',
          
          if( Month($1)>=4, Year($1), Year($1) -1) as FiscalYear Tagged '$fiscalyear', 
          
          Month($1) as Month Tagged '$month',
          
          if( Month($1)>=4, Month($1)-3,Month($1)+9) as FiscalMonthNumber Tagged '$FiscalMonthNumber',
          
          Date($1) as Date Tagged ('$date', '$day'),

          Week($1) as Week Tagged '$week',

          Weekday($1) as Weekday Tagged '$weekday',

          'Q' & Ceil(Month($1)/3)  as FinancialQuarter Tagged '$FinancialQuarter';
          

         DERIVE FIELDS FROM FIELDS [OrderDate] USING Calendar;

       

      table one contains OrderDate

       

      table two contains ProcessDate as OrderDate

       

      I have two tables that needs to link to this calendar but its causing a synthetic key.

       

      is there a way around it?

       

      Kind Regards

       

      JoeyBird

        • Re: calendar - synthetic key issue
          Gysbert Wassenaar

          Can you post the complete script?

            • Re: calendar - synthetic key issue
              Joanna Seldon

              Hiya

               

              i cant because of the data it contains..

               

              i can only give brief example

               

              Table 1

              CustomerID

              OrderDate

               

               

              i go on to use the OrderDate to define calendar fields

               

              Calendar: DECLARE FIELD DEFINITION TAGGED '$date'
              Parameters first_month_of_year = 1,
              fiscal_first_month_of_year = 4

              Fields

                  Year($1) As Year Tagged '$year',
                  
                  if( Month($1)>=4, Year($1), Year($1) -1) as FiscalYear Tagged '$fiscalyear', 
                  
                  Month($1) as Month Tagged '$month',
                  
                  if( Month($1)>=4, Month($1)-3,Month($1)+9) as FiscalMonthNumber Tagged '$FiscalMonthNumber',
                  
                  Date($1) as Date Tagged ('$date', '$day'),

                  Week($1) as Week Tagged '$week',

                  Weekday($1) as Weekday Tagged '$weekday',

                  'Q' & Ceil(Month($1)/3)  as FinancialQuarter Tagged '$FinancialQuarter';
                 

                 DERIVE FIELDS FROM FIELDS [OrderDate] USING Calendar;

               

              next table contains

               

              Table 2

              ShipperID

              ProcessDate as OrderDate  // I have to go this to get the field to link to the calendar fields but it causes synthtic key


              Please help

              joeybird x