10 Replies Latest reply: Aug 4, 2017 3:00 PM by Erica Trotter RSS

    Master Calendar not connecting to table

    Erica Trotter

      Hi,

      I am having issues with my Master calendar. I entered a field, but it is not connecting correctly to the original table, I attached screenshots of the issue. Thanks in advance if anyone can help!

        • Re: Master Calendar not connecting to table
          Sunny Talwar

          May be add Floor() to your date

           

          Date(Floor(START_DATE_TIME), 'MM/DD/YYYY') as START_DATE_TIME

           

          Capture.PNG

          • Re: Master Calendar not connecting to table
            Erica Trotter

            Thanks Sunny, I am actually training with Vinay and he gave me this question.

            I added that to the function but the issue is not resolved. .

              • Re: Master Calendar not connecting to table
                Sunny Talwar

                Can you copy and paste your script here?

                  • Re: Master Calendar not connecting to table
                    Erica Trotter

                    Here is the Machines Load Script:

                    MACHINE_TIME_INPUT:

                    LOAD  JOB_NUMBER as MCLINE_JO_KEY,

                    MACHINE_CODE&'-'&JOB_NUMBER as MCLINE_JO_KEY_MTI,

                       // COMPANY_CODE,

                      ///  FACTORY,

                      //  BATCH_NUMBER,

                       // SEQUENCE,

                        STATUS_FLAG as MTI_STATUS_FLAG,

                        MACHINE_CODE as MTI_MACHINE_CODE,

                        JOB_NUMBER as MTI_JOB_NUMBER,

                       // PROCESS_STAGE,

                       // RECIPE_LINE_NUMBER,

                        START_TIME,

                        END_TIME,

                        SETUP_TIME,

                        RUNTIME,

                        IF(if(WildMatch(MACHINE_CODE,'*DOWN*'), 'DOWN','UP')='DOWN',RUNTIME, 0) as Machine_Down,

                        IF(if(WildMatch(MACHINE_CODE,'*DOWN*'), 'DOWN','UP')='UP',RUNTIME,0) as Machine_UP,

                       

                        TIME_UNIT as MTI_TIME_UNIT,

                    //     COST_TIME_UNIT,

                    //     MACHINE_RECOVERY_FLAG,

                    //     MACHINE_RECOVERY_COST,

                        OUTPUT_QTY,

                        SYSTEM_DATE,

                        PERIOD,

                        "YEAR",

                        OPERATOR_ID,

                        Date(Floor(START_DATE_TIME), 'MM/DD/YYYY') as START_DATE_TIME,

                        MONTH(START_DATE_TIME) as month_mac,

                        date(START_DATE_TIME) AS START_DATE_TIME1 ,

                        END_DATE_TIME,

                        PM_DATE,

                    //     APS_SETUP_START_DATE,

                    //     APS_SETUP_END_DATE,

                    //     APS_MID_BATCH_DATE_TIME,

                        TIME_ENTRY_METHOD

                    //     OH_ALLOCATION_COST,

                    //     OH_ALLOCATION_FLAG,

                    //     RUNTIME_COST_ACTUAL,

                    //     SETUP_COST_ACTUAL,

                    //     OVERHEAD_COST_ACTUAL

                    FROM [lib://QVD L1 (farcnt_cpelton)/MACHINE_TIME_INPUT.qvd]

                    (qvd)

                    Where year(START_DATE_TIME)>=year(Today())-3;

                     

                     

                    MACHINES:

                    LEFT KEEP(MACHINE_TIME_INPUT)

                     

                    Here is the Master Calendar Load Script:

                    /// Preparing Calendar QVD//

                    tmp_MaxDate:

                    Load min((START_DATE_TIME)) as mindate,

                    max((START_DATE_TIME)) as maxdate;

                    Load Distinct  date(START_DATE_TIME,'MM/DD/YYYY') as START_DATE_TIME

                    FROM [lib://QVD L1 (farcnt_cpelton)/MACHINE_TIME_INPUT.qvd]

                    (qvd)  where Year(date(START_DATE_TIME))>=year(today())-3;

                     

                     

                    LET vMinDate = Peek('mindate',0,'tmp_MaxDate');

                    LET vMaxDate = Peek('maxdate',0,'tmp_MaxDate');

                    LET vToday = $(vMaxDate);

                    LET vTTM_MinDate= $(vMaxDate)-365;

                    LET vPTTM_MinDate= $(vMaxDate)-730;

                    LET vPPTTM_MinDate= $(vMaxDate)-1095;

                    //LET vAsOfDate =  num(Today())-1;  delete this variable

                    drop table tmp_MaxDate;

                     

                     

                    //Calendar AutoGenerate from Minimum Order date to Maximum START_DATE_TIME So that way we will have all dates even though we don't have transactions//

                    TEMP_CALENDAR:

                    LOAD

                    Date($(vMinDate) + RowNo() - 1) AS START_DATE_TIME

                    AUTOGENERATE

                    $(vMaxDate) - $(vMinDate) + 1;

                     

                    ////mapping tables

                    //MappingDOW:

                    //Mapping LOAD * INLINE [

                    //    Day, Day of Week

                    //    1, 1

                    //    2, 2

                    //    3, 3   

                    //    4, 4

                    //    5, 5

                    //    6, 5

                    //    7, 5

                    //];

                    //

                    //Creation of Master Calendar Using the AutoGenerate Calendar table by doing resident of  TEMP_CALENDAR//

                     

                     

                    MasterCalendar:

                    Load *,

                         if(mod(Month,3)=1,1,if(Mod(Month,3)=2,2,3)) as Quarter_Month_Serial_No,

                         IF(QuarterID=PEEK(QuarterID),PEEK(DayOfQuarter)+1,1)         AS DayOfQuarter

                    ;

                    LOAD

                    START_DATE_TIME,

                          START_DATE_TIME aS  START_DATE_TIME2,

                         ( Year(START_DATE_TIME)*12) + Month(START_DATE_TIME) as MonthIndex,

                           

                    Week(START_DATE_TIME) AS Week,

                    Year(START_DATE_TIME) AS Year,

                    Month(START_DATE_TIME) AS Month,

                    'Q' & ceil(Month(START_DATE_TIME)/3) AS  Quarter,

                        ceil(Month(START_DATE_TIME)/3) AS  QuarterID,

                    Day(START_DATE_TIME) AS Day,

                        Day(START_DATE_TIME) AS DayofMonth,

                    text(WeekDay(START_DATE_TIME)) AS WeekDay,

                        Month(START_DATE_TIME)*100+Day(START_DATE_TIME) as DayOfYear,

                    //ApplyMap('MapWeekDayNo',WeekDay(START_DATE_TIME)) AS WeekdayNo,

                    Date(monthstart(START_DATE_TIME), 'MMM-YYYY') AS MonthYear,

                    Year(START_DATE_TIME) &  num(Month(START_DATE_TIME), '00') AS YearMonth,

                         year(Today())&num(Month(Today()),'00') as Today_YearMonth,

                    WeekYear(START_DATE_TIME) & num(Week(START_DATE_TIME), '00')  AS YearWeek,

                    if(Year(START_DATE_TIME)=WeekYear(START_DATE_TIME), Week(START_DATE_TIME) - Week(MonthStart(START_DATE_TIME)) + 1,  Week(START_DATE_TIME) + 52 - Week(MonthStart(START_DATE_TIME)) + 1) AS [Week in Month],   //week number within a month, 1, 2, 3, 4, 5, 6

                    InYearToDate(START_DATE_TIME, $(vToday), 0) * -1 AS CurYTDFlag,

                    InYearToDate(START_DATE_TIME, $(vToday), -1) * -1 AS LastYTDFlag,

                    InMonthToDate(START_DATE_TIME, $(vToday), 0) * -1 as CurMTD_flag,

                    InMonthToDate(START_DATE_TIME, $(vToday), -1) * -1 as LastMTD_flag,

                    num(Month(START_DATE_TIME)) as MonthNo,

                    if(START_DATE_TIME<=date($(vMaxDate)),if(START_DATE_TIME>date($(vTTM_MinDate)),1,null()),null()) as TTM_Flag,

                    if(START_DATE_TIME<=date($(vTTM_MinDate)),if(START_DATE_TIME>date($(vPTTM_MinDate)),1,null()),null()) as PTTM_Flag,

                    if(START_DATE_TIME<=date($(vPTTM_MinDate)),if(START_DATE_TIME>date($(vPPTTM_MinDate)),1,null()),null()) as PPTTM_Flag

                     

                    RESIDENT TEMP_CALENDAR

                    ORDER BY START_DATE_TIME ASC;

                     

                     

                    DROP TABLE TEMP_CALENDAR;

                     

                    left join (MasterCalendar)

                    LOAD WeekDay,

                    WeekDayNo INLINE [

                        WeekDay, WeekDayNo

                        Mon, 1

                        Tue, 2

                        Wed, 3   

                        Thu, 4

                        Fri, 5

                        Sat, 6

                        Sun, 7

                    ];

                     

                     

                    //==================Variable Initialization for UI================

                     

                     

                    Let _PeriodSelector = 'TTM';

                    Let _PreviousPeriodSelector = 'PTTM';

                     

                    MaxDoX:

                    LOAD Max([DayOfYear]) as DoY

                           , Max([DayOfQuarter]) as DoQ

                           , Max([DayofMonth]) as DoM

                           ,max(QuarterID) as QID

                    //       , Max(QuarterIndex) as QtrIndex

                           , Right(MaxString(Month),3) as myMonth

                           ,max(Quarter_Month_Serial_No) as Quarter_Month_Serial_No1

                    //     , if(Match(Right(MaxString(Month),3), MonthNames) >= 3, 1, 0) as MarchOrLater

                    Resident MasterCalendar

                    where START_DATE_TIME = date($(vMaxDate));

                     

                     

                    LET vFixedDoY = Peek('DoY', 0, 'MaxDoX');

                    LET vFixedDoQ = Peek('DoQ', 0, 'MaxDoX');

                    LET vFixedDoM = Peek('DoM', 0, 'MaxDoX');

                    //LET vFixedCQIndex = Peek('QtrIndex', 0, 'MaxDoX');

                    LET vFixedMonth = Peek('myMonth', 0, 'MaxDoX');

                    LET vFixedCQtrIndex = Peek('QID', 0, 'MaxDoX');

                    Let vQtr_Mth_Serial_No = Peek('Quarter_Month_Serial_No1', 0, 'MaxDoX');

                     

                     

                    LET vCDoY = vFixedDoY;

                    LET vCDoQ = vFixedDoQ;

                    LET vCDoM = vFixedDoM;

                    LET vCM = vFixedMonth;

                    LET vPYDoY = vFixedDoY;

                    LET vPYDoQ = vFixedDoQ;

                      • Re: Master Calendar not connecting to table
                        Sunny Talwar

                        Try this

                         

                        MACHINE_TIME_INPUT:

                        LOAD  JOB_NUMBER as MCLINE_JO_KEY,

                        MACHINE_CODE&'-'&JOB_NUMBER as MCLINE_JO_KEY_MTI,

                          // COMPANY_CODE,

                          ///  FACTORY,

                          //  BATCH_NUMBER,

                          // SEQUENCE,

                            STATUS_FLAG as MTI_STATUS_FLAG,

                            MACHINE_CODE as MTI_MACHINE_CODE,

                            JOB_NUMBER as MTI_JOB_NUMBER,

                          // PROCESS_STAGE,

                          // RECIPE_LINE_NUMBER,

                            START_TIME,

                            END_TIME,

                            SETUP_TIME,

                            RUNTIME,

                            IF(if(WildMatch(MACHINE_CODE,'*DOWN*'), 'DOWN','UP')='DOWN',RUNTIME, 0) as Machine_Down,

                            IF(if(WildMatch(MACHINE_CODE,'*DOWN*'), 'DOWN','UP')='UP',RUNTIME,0) as Machine_UP,

                         

                            TIME_UNIT as MTI_TIME_UNIT,

                        //    COST_TIME_UNIT,

                        //    MACHINE_RECOVERY_FLAG,

                        //    MACHINE_RECOVERY_COST,

                            OUTPUT_QTY,

                            SYSTEM_DATE,

                            PERIOD,

                            "YEAR",

                            OPERATOR_ID,

                            Date(Floor(START_DATE_TIME), 'MM/DD/YYYY') as START_DATE_TIME,

                            MONTH(START_DATE_TIME) as month_mac,

                            date(START_DATE_TIME) AS START_DATE_TIME1 ,

                            END_DATE_TIME,

                            PM_DATE,

                        //    APS_SETUP_START_DATE,

                        //    APS_SETUP_END_DATE,

                        //    APS_MID_BATCH_DATE_TIME,

                            TIME_ENTRY_METHOD

                        //    OH_ALLOCATION_COST,

                        //    OH_ALLOCATION_FLAG,

                        //    RUNTIME_COST_ACTUAL,

                        //    SETUP_COST_ACTUAL,

                        //    OVERHEAD_COST_ACTUAL

                        FROM [lib://QVD L1 (farcnt_cpelton)/MACHINE_TIME_INPUT.qvd]

                        (qvd)

                        Where year(START_DATE_TIME)>=year(Today())-3;

                         

                         

                        MACHINES:

                        LEFT KEEP(MACHINE_TIME_INPUT)

                         

                        Here is the Master Calendar Load Script:

                        /// Preparing Calendar QVD//

                        tmp_MaxDate:

                        Load min((START_DATE_TIME)) as mindate,

                        max((START_DATE_TIME)) as maxdate;

                        Load Distinct  date(START_DATE_TIME,'MM/DD/YYYY') as START_DATE_TIME

                        FROM [lib://QVD L1 (farcnt_cpelton)/MACHINE_TIME_INPUT.qvd]

                        (qvd)  where Year(date(START_DATE_TIME))>=year(today())-3;

                         

                         

                        LET vMinDate = Peek('mindate',0,'tmp_MaxDate');

                        LET vMaxDate = Peek('maxdate',0,'tmp_MaxDate');

                        LET vToday = $(vMaxDate);

                        LET vTTM_MinDate= $(vMaxDate)-365;

                        LET vPTTM_MinDate= $(vMaxDate)-730;

                        LET vPPTTM_MinDate= $(vMaxDate)-1095;

                        //LET vAsOfDate =  num(Today())-1;  delete this variable

                        drop table tmp_MaxDate;

                         

                         

                        //Calendar AutoGenerate from Minimum Order date to Maximum START_DATE_TIME So that way we will have all dates even though we don't have transactions//

                        TEMP_CALENDAR:

                        LOAD

                        Date($(vMinDate) + RowNo() - 1) AS START_DATE_TIME

                        AUTOGENERATE

                        $(vMaxDate) - $(vMinDate) + 1;

                         

                        ////mapping tables

                        //MappingDOW:

                        //Mapping LOAD * INLINE [

                        //    Day, Day of Week

                        //    1, 1

                        //    2, 2

                        //    3, 3

                        //    4, 4

                        //    5, 5

                        //    6, 5

                        //    7, 5

                        //];

                        //

                        //Creation of Master Calendar Using the AutoGenerate Calendar table by doing resident of  TEMP_CALENDAR//

                         

                         

                        MasterCalendar:

                        Load *,

                            if(mod(Month,3)=1,1,if(Mod(Month,3)=2,2,3)) as Quarter_Month_Serial_No,

                            IF(QuarterID=PEEK(QuarterID),PEEK(DayOfQuarter)+1,1)        AS DayOfQuarter

                        ;

                        LOAD

                        START_DATE_TIME,

                              START_DATE_TIME as  START_DATE_TIME,

                            ( Year(START_DATE_TIME)*12) + Month(START_DATE_TIME) as MonthIndex,

                          

                        Week(START_DATE_TIME) AS Week,

                        Year(START_DATE_TIME) AS Year,

                        Month(START_DATE_TIME) AS Month,

                        'Q' & ceil(Month(START_DATE_TIME)/3) AS  Quarter,

                            ceil(Month(START_DATE_TIME)/3) AS  QuarterID,

                        Day(START_DATE_TIME) AS Day,

                            Day(START_DATE_TIME) AS DayofMonth,

                        text(WeekDay(START_DATE_TIME)) AS WeekDay,

                            Month(START_DATE_TIME)*100+Day(START_DATE_TIME) as DayOfYear,

                        //ApplyMap('MapWeekDayNo',WeekDay(START_DATE_TIME)) AS WeekdayNo,

                        Date(monthstart(START_DATE_TIME), 'MMM-YYYY') AS MonthYear,

                        Year(START_DATE_TIME) &  num(Month(START_DATE_TIME), '00') AS YearMonth,

                            year(Today())&num(Month(Today()),'00') as Today_YearMonth,

                        WeekYear(START_DATE_TIME) & num(Week(START_DATE_TIME), '00')  AS YearWeek,

                        if(Year(START_DATE_TIME)=WeekYear(START_DATE_TIME), Week(START_DATE_TIME) - Week(MonthStart(START_DATE_TIME)) + 1,  Week(START_DATE_TIME) + 52 - Week(MonthStart(START_DATE_TIME)) + 1) AS [Week in Month],  //week number within a month, 1, 2, 3, 4, 5, 6

                        InYearToDate(START_DATE_TIME, $(vToday), 0) * -1 AS CurYTDFlag,

                        InYearToDate(START_DATE_TIME, $(vToday), -1) * -1 AS LastYTDFlag,

                        InMonthToDate(START_DATE_TIME, $(vToday), 0) * -1 as CurMTD_flag,

                        InMonthToDate(START_DATE_TIME, $(vToday), -1) * -1 as LastMTD_flag,

                        num(Month(START_DATE_TIME)) as MonthNo,

                        if(START_DATE_TIME<=date($(vMaxDate)),if(START_DATE_TIME>date($(vTTM_MinDate)),1,null()),null()) as TTM_Flag,

                        if(START_DATE_TIME<=date($(vTTM_MinDate)),if(START_DATE_TIME>date($(vPTTM_MinDate)),1,null()),null()) as PTTM_Flag,

                        if(START_DATE_TIME<=date($(vPTTM_MinDate)),if(START_DATE_TIME>date($(vPPTTM_MinDate)),1,null()),null()) as PPTTM_Flag

                         

                        RESIDENT TEMP_CALENDAR

                        ORDER BY START_DATE_TIME ASC;

                         

                         

                        DROP TABLE TEMP_CALENDAR;

                         

                        left join (MasterCalendar)

                        LOAD WeekDay,

                        WeekDayNo INLINE [

                            WeekDay, WeekDayNo

                            Mon, 1

                            Tue, 2

                            Wed, 3

                            Thu, 4

                            Fri, 5

                            Sat, 6

                            Sun, 7

                        ];

                         

                         

                        //==================Variable Initialization for UI================

                         

                         

                        Let _PeriodSelector = 'TTM';

                        Let _PreviousPeriodSelector = 'PTTM';

                         

                        MaxDoX:

                        LOAD Max([DayOfYear]) as DoY

                              , Max([DayOfQuarter]) as DoQ

                              , Max([DayofMonth]) as DoM

                              ,max(QuarterID) as QID

                        //      , Max(QuarterIndex) as QtrIndex

                              , Right(MaxString(Month),3) as myMonth

                              ,max(Quarter_Month_Serial_No) as Quarter_Month_Serial_No1

                        //    , if(Match(Right(MaxString(Month),3), MonthNames) >= 3, 1, 0) as MarchOrLater

                        Resident MasterCalendar

                        where START_DATE_TIME = date($(vMaxDate));

                         

                         

                        LET vFixedDoY = Peek('DoY', 0, 'MaxDoX');

                        LET vFixedDoQ = Peek('DoQ', 0, 'MaxDoX');

                        LET vFixedDoM = Peek('DoM', 0, 'MaxDoX');

                        //LET vFixedCQIndex = Peek('QtrIndex', 0, 'MaxDoX');

                        LET vFixedMonth = Peek('myMonth', 0, 'MaxDoX');

                        LET vFixedCQtrIndex = Peek('QID', 0, 'MaxDoX');

                        Let vQtr_Mth_Serial_No = Peek('Quarter_Month_Serial_No1', 0, 'MaxDoX');

                         

                         

                        LET vCDoY = vFixedDoY;

                        LET vCDoQ = vFixedDoQ;

                        LET vCDoM = vFixedDoM;

                        LET vCM = vFixedMonth;

                        LET vPYDoY = vFixedDoY;

                        LET vPYDoQ = vFixedDoQ;