9 Replies Latest reply: Mar 28, 2017 3:05 PM by Apolo Takeshi Batista RSS

    How to Reload only a few sheets in script

    Apolo Takeshi Batista

      Hi there,

       

      I am facing the following problem

       

      I got many sheets in load like

      <sheet1 of script>

      table1:

      Load

      *;

      Select * from Dataset //the fields are A, B , C

      where date between 2013 and 2014

      </sheet1 of script>

       

      <sheet2 of script>

      table2:

      load *;

      Select * from Dataset //the fields are D, E, F

      where Date between 2014 and 2015

      </sheet2 of script>

       

      and so on.

      Could I somehow only reload <sheet2>?

      Since the fields are different I can not use something like conditionally where, so i need to do something like:

      1) using a Macro that reloads only the code inside sheet2

      2) using a condition to read or skip some part of the content, like skip 10 lines or go to next command

       

      Any tips? was the problem clear?

        • Re: How to Reload only a few sheets in script
          Peter Cammaert

          No, you cannot use/ignore script tabs by themselves or by switching them on/off. Script tabs are just a cosmetic separation between parts of the same script, to make it more manageable. The script is always executed sequentually, each tab in order and from first tab to the last tab.

           

          You can however change your code to make execution conditional, by:

          • using IF THEN ELSE END IF statements that - depending on a condition or the value of a variable - will execute a part or all of your script, or
          • you can use Partial/Full reload to only ADD/REPLACE table data, or to simply wipe out everything and start all over again, or
          • you can use include files in your main script to add/remove script parts by simply renaming the containing files, or
          • by selecting part of your script and selecting Edit->Comment from the menu in the script editor, or
          • ...

           

          Best,

           

          Peter

            • Re: How to Reload only a few sheets in script
              Apolo Takeshi Batista

              ok, but HOW would I do that?

               

              I know that it is just a cosmetic way, my question is:

               

              <sheet1 of script>

              table1:

              Load

              *;

              Select * from Dataset //the fields are A, B , C

              where date between 2013 and 2014

              </sheet1 of script>

               

              <sheet2 of script>

              table2:

              load *;

              Select * from Dataset //the fields are D, E, F

              where Date between 2014 and 2015

              </sheet2 of script>


              Execute "Reload" only table2, and commenting table1 full script.

               

              I don't think I can use something like:

              Let vExecute1 = '0'

              Let vExecute2 = '1'

              if vExecute2 = 0, then 'nothing'

                   else

                   load *;

              Select * from Dataset //the fields are D, E, F

              where Date between 2014 and 2015

              end if;

                • Re: How to Reload only a few sheets in script
                  Peter Cammaert

                  This has nothing to do with sheets or tabs. It has everything to do with how you would like to control the execution of your script. Do you prefer one variable to control execution of conditional blocks? Then you could use something like this:

                   

                  // Prepare some constants

                   

                  SET BLOCK1 = 1; // define individual bits as flags.

                  SET BLOCK2 = 2;

                  SET BLOCK3 = 4;

                   

                  // Now flag the blocks that should be executed here:

                   

                  LET vExecute = BLOCK1 + BLOCK3; // For example, just these two

                   

                  // Now do what is needed.

                  // Ignore Execution variable for all code that should always run

                   

                  IF vExecute bitand BLOCK1 THEN // Flag bit0 set

                    TRACE >>>> Executing Block1 code;

                    // Run the Block1 LOAD

                    LOAD ... FROM ...;

                  END IF

                   

                  IF vExecute bitand BLOCK2 THEN // Flag bit1 set

                    TRACE >>>> Executing Block2 code;

                    // Run the Block2 LOAD

                    LOAD ... FROM ...;

                  END IF

                   

                  IF vExecute bitand BLOCK3 THEN // Flag bit2 set

                    TRACE >>>> Executing Block3 code;

                    // Run the Block3 LOAD

                    LOAD ... FROM ...;

                  END IF

                   

                  // and so on

              • Re: How to Reload only a few sheets in script
                Carlos Monroy

                Hi Apolo,

                 

                As the script runs sequentially. You can defined variables in the first tab of the script to set up the run order, like this:

                 

                <Main Tab>

                SET vTab1 = 1;

                SET vTab2 = 0;

                </Main>

                 

                <Tab1>

                IF vTab1 =1 then

                Load ...

                ENDIF

                </Tab1>

                 

                and so on.

                 

                Or you can use subroutines.

                 

                Or you can move the tab you want to execute to the very first and then use 'Exit Script' to stop where you want to stop running.

                 

                Hope that helps,

                Carlos M