Qlik Community

Qlik Sense App Development

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

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
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;

View solution in original post

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;

View solution in original post