Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

twanqlik
Contributor

Data model - avoiding a loop

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.

1 Reply
vishsaggi
Esteemed Contributor III

Re: Data model - avoiding a loop

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;