topic Re: Master Calendar not connecting to table in App Development

Master Calendar not connecting to table

etrotter — Thu, 03 Aug 2017 18:18:27 GMT

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 — Thu, 03 Aug 2017 18:57:29 GMT

May be add Floor() to your date

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

Re: Master Calendar not connecting to table

etrotter — Thu, 03 Aug 2017 19:06:05 GMT

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 — Thu, 03 Aug 2017 19:33:46 GMT

Can you copy and paste your script here?

Re: Master Calendar not connecting to table

etrotter — Thu, 03 Aug 2017 19:40:01 GMT

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 — Thu, 03 Aug 2017 19:57:13 GMT

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,

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

etrotter — Thu, 03 Aug 2017 20:00:53 GMT

The following error occurred:

Field names must be unique within table

The error occurred here:

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

Re: Master Calendar not connecting to table

sunny_talwar — Fri, 04 Aug 2017 00:57:08 GMT

Can you share you app?

Re: Master Calendar not connecting to table

etrotter — Fri, 04 Aug 2017 15:35:37 GMT

Here is the file

Re: Master Calendar not connecting to table

sunny_talwar — Fri, 04 Aug 2017 17:28:02 GMT

Can you try now

changed to this:

/// Preparing Calendar QVD//

tmp_MaxDate:

Load min(START_DATE_TIME) as mindate,

max(START_DATE_TIME) as maxdate

Resident MACHINE_TIME_INPUT;

Re: Master Calendar not connecting to table

etrotter — Fri, 04 Aug 2017 19:00:34 GMT

It worked, thank you so much!