11 Replies Latest reply: Jan 22, 2018 8:46 AM by Beck Bakytbek RSS

    Comment out the part of script via sub routines

    Beck Bakytbek

      Hi Folks,

       

      i have the following situation and i am using in my script- area: the incremental load. It does look like:

       

      // main:

      // LOAD

      //     mit_id,

      //     mit_name,

      //     mit_gehalt,

      //     mit_datum

      // FROM [lib://Excel/Mitarbeiter.xlsx]

      // (ooxml, embedded labels, table is Tabelle1);

       

      // Store main into [lib://QVD/main.qvd];

       

      temp_main:

      load

          mit_id,

          mit_name,

          mit_gehalt,

          mit_datum

      FROM [lib://QVD/main.qvd]

      (qvd);

       

      Max_Value:

      load Max(mit_datum) as MaxWert

      Resident temp_main;

       

      let vNeu = peek('MaxWert',-1,'Max_Value');

       

      drop Table temp_main;

       

      main_neu:

      LOAD

          mit_id,

          mit_name,

          mit_gehalt,

          mit_datum

      FROM [lib://Excel/Mitarbeiter.xlsx]

      (ooxml, embedded labels, table is Tabelle1) where mit_datum >= $(vNeu);

       

      Concatenate

      LOAD

          mit_id,

          mit_name,

          mit_gehalt,

          mit_datum

      FROM [lib://QVD/main.qvd]

      (qvd)where not Exists(mit_id);

       

      Store main_neu into [lib://QVD/main.qvd];

       

      As you see, i commented out the first part of script. My questions is:

       

      Is there any possibilities or technique to comment out the first part of script via sub routines?

       

      Thanks a lot

      Beck

        • Re: Comment out the part of script via sub routines
          Petter Skjolden

          Yes you can "comment" out with sub routines. Another alternative is to use the multi-line comment starting with a /* and ending later in the script with a */.

           

          In your case it could look like:

           

          /*

          main:

          LOAD

               mit_id,

               mit_name,

               mit_gehalt,

               mit_datum

          FROM [lib://Excel/Mitarbeiter.xlsx]

          (ooxml, embedded labels, table is Tabelle1);

          Store main into [lib://QVD/main.qvd];

          */

           

          temp_main:

          load

              mit_id,

              mit_name,

              mit_gehalt,

              mit_datum

          FROM [lib://QVD/main.qvd]

          (qvd);

           

           

          or with a subrutine:

           

          Sub LoadMain

             main:

             LOAD

               mit_id,

               mit_name,

               mit_gehalt,

               mit_datum

            FROM [lib://Excel/Mitarbeiter.xlsx]

            (ooxml, embedded labels, table is Tabelle1);

            Store main into [lib://QVD/main.qvd];

          End Sub

           

          // CALL LoadMain; 

           

          temp_main:

          load

              mit_id,

              mit_name,

              mit_gehalt,

              mit_datum

          FROM [lib://QVD/main.qvd]

          (qvd);

            • Re: Comment out the part of script via sub routines
              Beck Bakytbek

              Hi Petter,

               

              thanks a lot for your help and time, the first option with /* i know that,

               

              but how can i comment out first part of script when i reloed the entire script, shoud i create any conditions or if..end, do you have any idea? or how can check that i store the date in my folder and my folder is not empty?

               

              thanks a lot

              Beck

                • Re: Comment out the part of script via sub routines
                  omar bensalem

                  U want to execute only the first part once? (if the qvd is not created?) and then comment it?

                  and use the other script?

                   

                  What's the condition once achieved u want to comment ur script beck?

                    • Re: Comment out the part of script via sub routines
                      Beck Bakytbek

                      Hi Omar,

                       

                      thanks a lot for your responce,

                      i want exclude the first part of script when i cretae a qvd

                        • Re: Comment out the part of script via sub routines
                          omar bensalem

                          so basically, if the qvd do not exist, execute the first script;

                          if the qvd exists , execute the second part; is that so?

                           

                          try sthing like :

                          - handle error mode (qlik won't stop if it finds errors) : SET ErrorMode=0;

                          - Try to import the main table from the qvd :

                          * if it does not exist (ScriptError >0):

                          wait 2 seconds then create ur qvd (the first script)


                          else (if it does exist (the qvd)):

                          => drop ur main table u just created (to test the existing of the qvd)

                          and run ur second script:



                           

                           

                           

                           

                          SET ErrorMode=0;

                          main:

                          LOAD

                              mit_id,

                              mit_name,

                              mit_gehalt,

                              mit_datum

                          FROM [lib://QVD/main.qvd];

                           

                          if ScriptError >0

                           

                          then

                           

                          sleep 2000;

                          main:

                          LOAD

                              mit_id,

                              mit_name,

                              mit_gehalt,

                              mit_datum

                          FROM [lib://Excel/Mitarbeiter.xlsx]

                          (ooxml, embedded labels, table is Tabelle1);

                          Store main into [lib://QVD/main.qvd];

                           

                          else

                           

                          drop table main;

                           

                          temp_main:

                          load

                              mit_id,

                              mit_name,

                              mit_gehalt,

                              mit_datum

                          FROM [lib://QVD/main.qvd]

                          (qvd);

                           

                          Max_Value:

                          load Max(mit_datum) as MaxWert

                          Resident temp_main;

                           

                          let vNeu = peek('MaxWert',-1,'Max_Value');

                           

                          drop Table temp_main;

                           

                          main_neu:

                          LOAD

                              mit_id,

                              mit_name,

                              mit_gehalt,

                              mit_datum

                          FROM [lib://Excel/Mitarbeiter.xlsx]

                          (ooxml, embedded labels, table is Tabelle1) where mit_datum >= $(vNeu);

                           

                          Concatenate

                          LOAD

                              mit_id,

                              mit_name,

                              mit_gehalt,

                              mit_datum

                          FROM [lib://QVD/main.qvd]

                          (qvd)where not Exists(mit_id);

                           

                          Store main_neu into [lib://QVD/main.qvd];

                           

                          EndIf

                           

                           

                           

                          set ErrorMode=1;

                  • Re: Comment out the part of script via sub routines
                    Marcus Sommer

                    In most of my loadings I use a variable (loaded from an external excel) which is queried within an if-loop to determine how the load should be executed. Simplified it looked:

                     

                    if '$(vLoadArt)' = 'full' then

                         load * from Source;

                    elseif '$(vLoadArt)' = 'partial' then

                         load * from qvd where Period <= $(vPeriod);

                              concatenate

                         load * from Source where Period > $(vPeriod);

                    elseif '$(vLoadArt)' = 'incremental' then

                         load * from qvd;

                              concatenate

                         load * from Source where not exists(Key);

                    end if

                     

                    But just to comment out the initial load or some further load-parts is not a bad idea and quite practically - the normal case will be the incremental loading and if something unusual happens which requires a full-load and/or some adjustements to the load-statement you could just apply these things manually. An automatic way of handling any exceptions will be quite expensive and often even not possible.

                     

                    - Marcus