9 Replies Latest reply: Feb 13, 2014 11:51 AM by Michael Solomovich RSS

    Variables in Load Script

    Nate Schroeder

      I was wondering if it's possible to use variables in the load script and either prompt the user for input, or pull the values from an object in the application like a date picker.  The reason for this is that I'm using some SQL to pull three consecutive months worth of data from a database.  I'm usually pulling for the last three months.  I would like to be able to change which months are loaded without manually editing the load script each month.

       

      Thank you,

      -Nate

        • Re: Variables in Load Script

          Yes you can use variables inside the script

          do sth like $(myVar)

           

          No, you cannot ask the user during the script

          Yes, can link this variable to a QV object; like InputBox or Slider for example

           

          Because your script must be OK in all cases, either use the alt() function in case your variable is null (to set a default) or exit the script

          sth like:

          let myVar = alt(myVar, 3)

          or

          if isnull(myVar) ...

           

          When you run batch, you may also set this variable.

           

          I had published a long doc on LOAD. You will find more explanations into it

          : http://community.qlik.com/docs/DOC-5698

           

          Fabrice

          • Re: Variables in Load Script
            Massimo Grossi

            I think you can use

             

            - an external file as Fabrice said

             

            - an input box UI object linked to a variable

             

            - this function in the script

            Input(str cue [, str caption])

            This function can be used in the script only and opens an input box that prompts the user for a value during the script execution. The parameters cue and caption are used as message and caption texts, respectively. The function returns the entered value.

            The input box function returns NULL if the dialog is cancelled, closed or cannot be shown.

            Example:

            Load
            Input('Enter value', 'Input box') as v,
            Recno () as r
            autogenerate 3;

            • Re: Variables in Load Script
              Michael Solomovich

              There is no need to edit the script each month.  The current date is always known, so you can calculate the start date of the data you need - it could be "three months back" or "the start of the month three months back", or whatever rule you can think of.  Create a variable according to your rule at the beginning of the script and use it later in the script as a condition to filter the records.

              A good habit is to delete this variable at the end of the script if you don't need it on the front end.  Or vote here http://community.qlik.com/ideas/1909 for Rob's idea.

              • Re: Variables in Load Script

                Hi Nate,

                 

                This is a different approach, but it might help you.

                 

                You can use an external file to store your variable. You can set your variables in a fil such as "variables.qvs".

                 

                In the file define your variables. For example:

                 

                SET vMonthStart = 1;

                SET vMonthEnd = Month(Today());

                 

                On your load script use an Include sentence, like this:

                 

                $(Include=<file path>variables.qvs);

                 

                After including the variables, use them on your script.

                 

                Should you need to select a different period of time, you will only have
                to change the variable values on your file (instead of altering the load script).