5 Replies Latest reply: Jun 19, 2017 9:35 AM by Giacinto Abbruzzese RSS

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

    Giacinto Abbruzzese

      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

       

          • Re: How to generate a daily plan schedule for a certain time intervall?
            Giacinto Abbruzzese

            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!

              • Re: How to generate a daily plan schedule for a certain time intervall?
                Stefan Wühl

                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"

                ;