9 Replies Latest reply: Jun 5, 2016 5:36 PM by Stefan Wühl RSS

    Advise - scripting

    Joris Lansdaal

      Hi,

      I would like some advise and would like to know if I my below script is written efficient.Do I need to reload the tables as much as I do to create the new variables?

      Thanks.

       

      Temp_Demand_2:

      Load

      BQS_ind,

      BQS_oms,

      Weekday_Flag,

      Demand_Datum,

      Demand_Datum_Month,

      BQS_Dat_ID,

      If(isnull(Demand_Aantal)=-1,0,Demand_Aantal) as Demand_Aantal,

      Datum,

      Flag_uit,

      Leverancier_Naam,

      Lopend_Crediteurnr,

      Lopend_Crediteuroms,

      Lopend_Invoerdat,

      Lopend_Afldat,

      Lopend_Orderbak,

      Lopend_Order_interval,

      Lopend_Overdue,

      Lopend_confirmatie,

      Lopend_Open,

      Lopend_Order,

      If(isnull(Lopend_Aantal_B)=-1,0,Lopend_Aantal_B) as Lopend_Aantal_B,

      If(isnull(Lopend_Aantal_O)=-1,0,Lopend_Aantal_O) as Lopend_Aantal_O,

      If(isnull(Lopend_Aantal_T)=-1,0,Lopend_Aantal_T) as Lopend_Aantal_T,

      Artnr,

      Lopend_Artnr,

      Lopend_Artoms,

      Joris,

      1 as Flag,

      If(isnull(Voorraad)=-1,0,Voorraad) as Voorraad,

      //---------------------------------------Projectie Fact-------------------------------------------------------

      If(isnull(Demand_Aantal)=-1,0,Demand_Aantal)*-1+

      If(isnull(Lopend_Aantal_B)=-1,0,Lopend_Aantal_B) +

      If(isnull(Voorraad)=-1,0,Voorraad) as Projectie_step

      resident Temp_Demand

      where Flag_uit=0 and isnum(BQS_ind)

      Order by BQS_ind asc, Datum asc, Demand_Aantal desc;

       

      //-------------------------Nu maken we een rangesum van Projectie_Step-----------------------------------------

       

      Temp_Projectie_1:

      Load *,

      BQS_ind&Datum as %BQS_Datum_Key,

      ApplyMap('Voorraadprijs_max',BQS_ind,0)as Voorraadprijs_max,

      ApplyMap('Voorraadprijs_min',BQS_ind,0)as Voorraadprijs_min,

      if(BQS_ind=Peek(BQS_ind),RangeSum(Projectie_step,Peek(Projectie2)),RangeSum(Projectie_step)) as Projectie2

      Resident Temp_Demand_2;

       

       

      //-------------------------Omdat er meerdere lijnen op 1 dag kunnen zijn moeten we de max bepalen---------------

       

       

      Projectie:

      Mapping

      Load

      BQS_ind&Datum as %BQS_Datum_Key,

      Max(Projectie2) as Projectie

      resident Temp_Projectie_1

      Group by BQS_ind&Datum ;

       

       

      // ------------------------- Met Peek plaatsen we de max waarde op de eerste lijn en een 0 op de andere-------------------

       

       

      Demand:

      Load *,

      if(if(%BQS_Datum_Key=Peek(%BQS_Datum_Key),0,ApplyMap('Projectie',%BQS_Datum_Key,0))<0,0,if(%BQS_Datum_Key=Peek(%BQS_Datum_Key),0,ApplyMap('Projectie',%BQS_Datum_Key,0)))as Projectie

      resident Temp_Projectie_1;

      //------------------------- nu hebben we de juiste dagprojecties op de weekdagen --------------------------------------

       

       

      Drop table Temp_DagVoorraad, Temp_Demand, Temp_Demand_2, Temp_Projectie_1;

      Store Demand into $(vStorePath)\Datamodel_Projection.CSV (txt);

        • Re: Advise - scripting
          Peter Cammaert

          Can you explain what the script should do/find? The way you formatted your load script doesn't exactly make it look like a User Manual...

            • Re: Advise - scripting
              Joris Lansdaal

              Good point about the formatting. Are there any guidelines on how to improve script readability; next to the format?

              • Re: Advise - scripting
                Joris Lansdaal

                The script your looking at needs to generate one measure named Projectie. There can be multiple lines per BQS_ind - Date combination. I need one value per BQS_Ind -Date combination for Projectie.

                 

                1) First I created Temp_Demand2 where I loaded/concatenated 3 different sources. Inventory, Orders and future Demand. Three measures combined gives me the first step for projection measure.

                2) Via a correct sorting and rangesum I can extract a Max value and apply mapping.

                3) in order to apply one value and zero's on the other lines I created the for the Demand table.

                 

                It works, but is ask myself if I need to create all the different tables?

                  • Re: Advise - scripting
                    Stefan Wühl

                    You might be able to use a preceding load instead of resident loads, e.g. when looking at your first two table load statements.

                    Preceding Load

                     

                    Note that there recently were discussions about the performance costs of a preceding load being to high, so test with your data to ensure the costs are not too high.

                     

                    You may also be able to replace the aggregating mapping tables and additional resident loads with the ApplyMap() functions with loading your fact table two times, once sorted asc, once des, to create the entries for the first and last line of your grouping entities.

                    • Re: Advise - scripting
                      Peter Cammaert

                      The table you can drop for sure is the mapping table. If you don't have billions of rows, replace it with

                       

                      LEFT JOIN (Temp_Projectie_1)

                      LOAD BQS_ind, Datum, Max(Projectie2) as NieuweProjectie

                      RESIDENT Temp_Projectie_1

                      GROUP BY BQS_in, Datum;

                       

                      and in your next step, you use the new field instead of the original one.

                       

                      Also (but this doesn't elimnate any tables, it's just shorter in writing) all the IF() tests for null values can be replaced by the Alt() function which does the same. For example

                       

                      :

                      If(isnull(Demand_Aantal)=-1,0,Demand_Aantal) as Demand_Aantal,

                      :

                       

                      becomes

                       

                      :

                      Alt(Demand_Aantal, 0) AS Demand_Aantal,

                      :

                       

                      BTW IMHO GROUP BY can only group on field names, not on expressions (which 'BQS_in & Datum' is) Don't you get any errors?

                       

                      Best,

                       

                      Peter

                        • Re: Advise - scripting
                          Stefan Wühl

                          Hi Peter,

                           

                          AFAIK, GROUP BY can indeed group by expression, I am using this like

                           

                          ...

                          GROUP BY MonthName(DATEFIELD);

                           

                          so using a combined key that doesn't exist in the input table should also work (which should be the same than grouping on the fields separately).

                            • Re: Advise - scripting
                              Peter Cammaert

                              Thanks Stefan. I should search for an experiment I did a few years back, when the use of ad-hoc fields always made the GROUP BY fail. I gave up since then. Remember discussing the state of QlikView documentation? This is what it says in the help article on the LOAD statement:

                               

                              At the top: "[ group_by groupbyfieldlist ]"

                               

                              In the discussion for group by: "groupbyfieldlist ::= (fieldname { ,fieldname } )"


                              and back to the top: "fieldname is a text that is identical to a field name in the table. "

                               

                              The on-line help isn't any different. I know that my restricted field-of-vision is caused in part by Qlik information It's still a pity that they don't do anything about it...

                               

                              Peter

                                • Re: Advise - scripting
                                  Stefan Wühl

                                  The on-line help isn't any different. I know that my restricted field-of-vision is caused in part by Qlik information It's still a pity that they don't do anything about it...

                                   

                                  Peter

                                  Hi Peter,

                                   

                                  yeah, I remember that discussion pretty well.

                                   

                                  I fully agree that the help pages should be reviewed more often.

                                   

                                  It would really be good if we somehow can post comments / issues to the help page technical writers.

                                   

                                  Best

                                  Stefan

                        • Re: Advise - scripting
                          Joris Lansdaal

                          Peter and Stefan,

                          I really appreciate both your help, thanks!

                          Joris