Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

etrotter
Contributor II

Master Calendar not connecting to table

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!

1 Solution

Accepted Solutions

Re: Master Calendar not connecting to table

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;

10 Replies

Re: Master Calendar not connecting to table

May be add Floor() to your date

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

Capture.PNG

etrotter
Contributor II

Re: Master Calendar not connecting to table

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

Can you copy and paste your script here?

etrotter
Contributor II

Re: Master Calendar not connecting to table

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

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;

etrotter
Contributor II

Re: Master Calendar not connecting to table

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

Can you share you app?

etrotter
Contributor II

Re: Master Calendar not connecting to table

Here is the file

Re: Master Calendar not connecting to table

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;

Community Browser