How to generate a daily plan schedule for a certain time intervall?

[gino2780]

Hello,

i'm currently trying to figure out how i can program my current Inline-Qlik-script more efficiently.

The Overall Task is to create a data matrix consisting of a certain amount of fields with it's values for a specific time intervall (start date & end date) to set up an plan schedule on a daily basis in order to be able to make pivot tables for example.

Now, i'd like to insert as little as possible commands in the qlik script to generate this data (see data matrix below)

that qlik most likely auto-generates the corresponding fields and values (date, day and work day; based on the time intervall (Start date -> End date)) accordingly without typing each and every field and value in a typical Inline list -> imagine having larger time intervalls, then the script would have no end, bc you have to write down all the data manually in the script

[sunny_talwar]

May be using a while loop or Interval Match...

Interval match

[swuehl]

Yes, use a while loop:

Creating Reference Dates for Intervals

[gino2780]

Thanks for the Link. The HIC-approach (while loop) worked perfectly well, although im trying to figure out, how i can generate two additional variables in this context to differentiate between netdays and working days without adding further (long) inline tables. Is this only possible with another matching table?

Currently my script looks like this (Step 1 + Step 2):

////Step 1: Autogenerate Timeschedule with the "while loop..." ////

Kapa:

LOAD

    "Start date",

    "End date",

    "planed quantities",

    "factory id"

FROM [lib://Desktop/Test_Kapa_neu.xlsx]

(ooxml, embedded labels, table is Tabelle1);

"Kapa_SI_Zeitplan":

Load

"factory id",

        "planed quantities" / ("End date" - "Start date" + 1) as täglicheMengen,

        Date("Start date" + IterNo()-1) as Datum

        Resident Kapa

        While IterNo() <= "End date" - "Start date" + 1;

In order to add the WorkingDays and NetDays variable, i've also added two more tables below the do while script (above) and "connected" the tables via Datum field:

////Step 2: Add Inline tables to generate WorkinDays and NetDays variables; joined via Datum field with the while loop Datum////

Holidays:

Load concat(num(date#(Holiday,'DD.MM.YYYY')) ,',') as Holidays

Inline [

Holiday

05.06.2017

15.06.2017

];

LET vHolDays = peek('Holidays');

Calendar:

//Load *, NumMax(0,NetWorkDays(Date#(Date,'DD/MM/YYYY'),today(1),'25/12/2012','26/12/2012','01/01/2013','25/12/2013','26/12/2013')-1) as NetDays

Load *, NumMax(0,NetWorkDays(Date#(Datum,'DD.MM.YYYY'),today(1),$(vHolDays))-1) as NetDays

Inline [

Datum, WorkingDay

13.06.2017, 1

14.06.2017, 1

15.06.2017, 0

16.06.2017, 1

17.06.2017, 0

18.06.2017, 0

19.06.2017, 1

20.06.2017, 1

];

The outcome in a pivot looks quite consistent to me and is what i wanted to achieve,

Although, i'm looking for a possibilty to integrate the WorkingDay variable (and in best case also the NetDay variable) into the while loop in order to make the script less long, e.g. get rid of both inline tables (Holidays & Calendar) at the end.

Thank you so far Stefan and Sunny!

[swuehl · Accepted solution]

I think you need the holiday calendar (you can load the table from an excel file or a database, but I think you need to define the holidays somewhere), but you can get rid of the calendar inline table by using one of the common script snippets for a master calendar, e.g.

////Step 1: Autogenerate Timeschedule with the "while loop..." ////

Kapa:

LOAD

    "Start date",

    "End date",

    "planed quantities",

    "factory id"

INLINE [

Start date, End date, planed quantities, factory id

13.06.2017,20.6.2017,6000,2

13.06.2017,20.6.2017,3000,4

01.06.2017,20.6.2017,6000,9

];

"Kapa_SI_Zeitplan":

Load

"factory id",

        "planed quantities" / ("End date" - "Start date" + 1) as täglicheMengen,

        Date("Start date" + IterNo()-1) as Datum

        Resident Kapa

        While IterNo() <= "End date" - "Start date" + 1;

//In order to add the WorkingDays and NetDays variable, i've also added two more tables below the do while script (above) and "connected" the tables via Datum field:

////Step 2: Add Inline tables to generate WorkinDays and NetDays variables; joined via Datum field with the while loop Datum////

Holidays:

Load concat(num(date#(Holiday,'DD.MM.YYYY')) ,',') as Holidays

Inline [

Holiday

05.06.2017

15.06.2017

];

LET vHolDays = peek('Holidays');

MasterCalendar:

LOAD

  TempDate AS Datum,

  Year(TempDate) AS Year,

  Month(TempDate) AS Month,

  Weekday(TempDate) AS WeekDay,

NetWorkDays(TempDate,TempDate,$(vHolDays)) as WorkingDay,

  NumMax(0,NetWorkDays(TempDate,today(1),$(vHolDays))-1) as NetDays

;

//=== Generate a temp table of dates ===

LOAD

  date(mindate + IterNo()) AS TempDate

  ,maxdate // Used in InYearToDate() above, but not kept

WHILE mindate + IterNo() <= maxdate

;

//=== Get min/max dates from fact table ===/

LOAD

  min(Datum)-1 as mindate,

  max(Datum) as maxdate

RESIDENT "Kapa_SI_Zeitplan"

;

[gino2780]

Thank you! Worked quite well.