1 Reply Latest reply: Sep 20, 2016 2:19 PM by Nick Hoff RSS
      • Re: Can I make more than two Associations
        Nick Hoff

        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;