Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Can I make more than two Associations

How can I associate more than two objects. For example I try to associate four months but I can only do pairs.

Thanks

Regards

1 Reply
Highlighted
Specialist
Specialist

All associations in your data model are made by a key field.  You should only use one key field between two tables anymore will give you a synthetic key.  If you are working with dates, you should build a master calendar and associate it with the date field in your data.  You can use the example below if you need a master calendar script, just have to edit the variables to work with your data.

//Calendar:

LET vDateMin = Num(MakeDate(2010,1,1));
//LET vDateMax = Floor(MonthEnd(Today())); 
LET vDateMax = Num(MakeDate(2020,6,30));
LET vDateToday = Num(Today()); 
LET PD = Date(makedate(2010,1,15));
LET vFactor = 6; //offset for calculating fiscal year

TempCalendar: 
//Left Keep (PayCore)
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber
Date($(vDateMin) + RowNo() - 1) AS TempDate 
AUTOGENERATE
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax)

SCM_CALENDAR_MASTER: 
LOAD
Date(TempDate) AS CalendarDate
Date(TempDate) AS  %DATE_KEY,

// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth
WeekDay(TempDate) AS CalendarDayName
Week(TempDate) AS CalendarWeekOfYear
Month(TempDate) AS CalendarMonthName
'Q' &
Ceil(Month(TempDate)/3) AS CalendarQuarter
Year(TempDate) AS CalendarYear

// Calendar Date Names 
WeekName(TempDate) as CalendarWeekNumberAndYear
MonthName(TempDate) as CalendarMonthAndYear
QuarterName(TempDate) as CalendarQuarterMonthsAndYear

// Start Dates 
DayStart(TempDate) as CalendarDayStart
WeekStart(TempDate) as CalendarWeekStart
MonthStart(TempDate) as CalendarMonthStart
QuarterStart(TempDate) as CalendarQuarterStart
YearStart(TempDate) as CalendarYearStart

// End Dates 
DayEnd(TempDate) as CalendarDayEnd
WeekEnd(TempDate) as CalendarWeekEnd
MonthEnd(TempDate) as CalendarMonthEnd
QuarterEnd(TempDate) as CalendarQuarterEnd
YearEnd(TempDate) as CalendarYearEnd

// Combo Date Examples 
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter
'Wed ' &
DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays

//Fiscal Dates
Num(Month(AddMonths(TempDate,$(vFactor))),00) AS FiscalMonthNum,
'FY ' &
Right(Year(AddMonths(TempDate,$(vFactor))),2) AS FiscalYearDesc,
Year(AddMonths(TempDate,$(vFactor)))&'|'&Num(Month(AddMonths(TempDate,$(vFactor))),00) as %Fiscal_Key,
Year(AddMonths(TempDate,$(vFactor)))&'-'&Num(Month(AddMonths(TempDate,$(vFactor))),00) as FYRPR,
Year(AddMonths(TempDate,$(vFactor))) as FiscalYear,
'FY ' &
Right(Year(AddMonths(TempDate,$(vFactor))),2)& ' Q' & Ceil(Month(AddMonths(TempDate,$(vFactor)))/3) AS FiscalYearQuarter

RESIDENT TempCalendar ORDER BY TempDate ASC

DROP TABLE TempCalendar;