1 Reply Latest reply: Jan 25, 2017 12:40 PM by Vishwarath Nagaraju RSS

    Data model - avoiding a loop

    Twan Peters

      I want to connect 2 fact tables to one Calender and to 1 dimension, however i'm not able to do that without creating a loop.

       

      I've attached my data model, i think i did something wrong in my data model

       

      Basically what i want:

       

      1) Filter on source_system_code

      Result: data from 'Fact_ConsumerRegistrations' + data from 'Sell_Out data'

       

      2) Filter on data:

      Result: data from 'Fact_ConsumerRegistrations' + data from 'Sell_Out data'



      Currently i'm able to do the first part, but not the second part.

        • Re: Data model - avoiding a loop
          Vishwarath Nagaraju

          No harm in creating a multi calendar (i mean another calendar) in your data model and link it to your sell out data table.

           

          Try this and see.

           

           

          MasterCalendar:

          Load

          Num(Floor(TempDate))                                   AS %PROD_KeyReportDate,

           

          week(TempDate)                                         AS PROD_Week,

          Year(TempDate)                                         AS PROD_Year,

          Month(TempDate)                                        AS PROD_Month,

          Day(TempDate)                                          AS PROD_Day,

          YeartoDate(TempDate)*-1                                AS PROD_CurYTDFlag,  

          YeartoDate(TempDate,-1)*-1                             AS PROD_LastYTDFlag,  

          inyear(TempDate, Monthstart($(varMaxDate)),-1)         AS PROD_RC12,  

          date(monthstart(TempDate), 'MMM-YYYY')                 AS PROD_MonthYear,   

          'Q' & ceil(month(TempDate) / 3)                        AS PROD_Quarter,

          Week(weekstart(TempDate)) & '-' & WeekYear(TempDate)   AS PROD_WeekYear,

          WeekDay(TempDate)                                      AS PROD_WeekDay

          ;

           

           

          //=== Generate a temp table of dates ===

          LOAD

                date(mindate + IterNo()) AS TempDate,

                maxdate // Used in InYearToDate() above, but not kept

          WHILE mindate + IterNo() <= maxdate;

           

           

          //=== Get min/max dates from Field ===/

          LOAD

          min(FieldValue('ReportDate', recno()))-1        AS mindate,

          max(FieldValue('ReportDate', recno()))          AS maxdate

          AUTOGENERATE FieldValueCount('ReportDate');

          //**************************************************************************/

          And Create a keyreportdate in your selloutdata table like

           

          SELL_OUT_DATA:

          LOAD yourcolumns,

                    Num(Floor(ReportDate))   AS PROD_KeyReportDate

          FROM yourdatasourcetable;