Data model - avoiding a loop

twanqlik — Wed, 25 Jan 2017 17:12:17 GMT

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

vishsaggi — Wed, 25 Jan 2017 17:37:02 GMT

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;