topic Re: Variables in Load Script in QlikView

Variables in Load Script

dukane24 — Thu, 13 Feb 2014 14:18:09 GMT

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

Thu, 13 Feb 2014 14:29:35 GMT

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)

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

maxgro — Thu, 13 Feb 2014 14:34:14 GMT

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

rwunderlich — Thu, 13 Feb 2014 14:36:10 GMT

"No, you cannot ask the user during the script"

You can use the input() script function to prompt for user input.

-Rob

Re: Variables in Load Script

Thu, 13 Feb 2014 14:49:25 GMT

You are absolutely right

You can also use Input() function. Test in case the user presses Cancel.

Sorry for that (in fact, i do not know why, I never used it)

Fabrice

Re: Variables in Load Script

Anonymous — Thu, 13 Feb 2014 15:19:13 GMT

I guess you don't use it for the same reason as most of us - you want to run unattended reload.

Re: Variables in Load Script

Anonymous — Thu, 13 Feb 2014 15:25:18 GMT

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

Thu, 13 Feb 2014 15:39:51 GMT

Michael,

Either the scope is knwon (95 % of the scripts) and no use to ask. As you said, it may be computed (the last 3 months for ex ending today or yesterday ...)

Or the user may chose its scope (I guess 5%). To me, it is easier in the UI to give the choice, to check the input (the min, the max, the data type ...)

The user can enter numeric or text in the input box. He may press Cancel (variable becomes null) .... Many many tests to be sure that the input is as expected.

Fabrice

Re: Variables in Load Script

Thu, 13 Feb 2014 16:13:45 GMT

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).

Re: Variables in Load Script

Anonymous — Thu, 13 Feb 2014 16:51:52 GMT

Mostly agree.

I didn't use input in the script besides some basic testing, and don't remember the details. I'm thinking now that there could be (if not - should be!) an option for the unattended reload. Say, if there is no user input for some reasonable time, use something else defined in the script as a default value.