topic Re: Difference between LET and SET? in QlikView

Difference between LET and SET?

Thu, 08 May 2014 08:27:18 GMT

Difference between LET and SET?

Re: Difference between LET and SET?

ThornOfCrowns — Thu, 08 May 2014 08:29:48 GMT

Let and Set commands are used to define Script Variables. Any text or numeric value can be assigned to the script variables.

Examples:

Set V = $ ; ( Set- assigns the variable(V) the text(String) to the right of the equal sign)

Let vStart = num(makedate(2011)); (Let- evaluates the expression)

Set MUL = ’$1*$2’;

Set X = $(MUL(3,7)); // returns '3*7' in X
Let X = $(MUL(3,7)); // returns 21 in X

Re: Difference between LET and SET?

Thu, 08 May 2014 08:45:35 GMT

A variable is an entity used to contain a single data value which can be a number, a character or a string.

The variable acquires its value from LET or SET in the Script or from an Input Box in a layout which can be set by users.

There are few ways to create variables.

1. Variables Tab in the Document Properties
2. New Variables in New Input Box Properties
3. Variable Overview in Settings Menu
4. Using ‘SET’ or ‘LET’ in Edit Script

If the first character of the string starts with an equal sign ‘ = ‘, QlikView evaluates it as a formula and stores the evaluated result in it. Otherwise, it holds the value as a string. You can store a formula with equal sign in front, then you can just use the variable in the expression without Dollar Sign Expansion, if it is storing a string as a formula, then you need to use the Dollar Sign Expansion: ‘$(variablename)’.

i.e. Let’s create two variables: vString1 and vString2. vString1 stores a string ’1+3′ and vString2 stores a string ‘=1+3′.

Create a new Text Object and type ‘=vString1′, you will see ’1+3′ in the text object.

Then create another Text Object and type in ‘=$(vString1), then Dollar Sign Expansion evaluates the formula and you will get ’4′.

Then create another Text box and type in ‘=vString2′, you will see ’4′ in it.

‘LET’ and ‘SET’ are used for defining Variables in the script. The variables can be used for substituting strings, paths, drives, etc.

‘SET’ treats everything after the equal sign (=) as String. If it contains space, then you need double quote in the beginning and the end.

The syntax is: set variablename=string

Examples:

Set FileToUse=Data1.csv;

Set Constant=”My string”;

Set BudgetYear=1997;

‘LET’ evaluates the expression after the equal sign (=) and stores the value.

The syntax is: let variablename=expression (Note: the word ’let’ can be omitted. )

Examples

Let x=Today(); >> x=12/01/2012

Let y=’Today()’; >> y=Today()

Since the function, Today(), is encapsulated by the single quotes, QlikView treats it as a string. After running the script and from the Variable Overview in the Settings menu, you will find it is holding a string ‘Today()’.

In the Edit Script, if you are assigning variables with ‘Set’, QlikView will store whatever you typed in as a string. If you assigned a formula in a variable, and you want to use it in QlikView you need to use dollar sign expansion. However, if you assigned an equal sign in the beginning of the string, for example, “=Today() + 7″, you can just use the variable name without Dollar sign expansion since the variable will evaluate the formula and be represented as if it were a number.

If you are assigning variables with ‘Let’, you have to encapsulate the formula in between single quotes ( ‘ ). If you use double quotes, the variable gets a null value and the variable will not be created.

If you want to create a variable which hold a dollar sign expansion, then you need to separates the equation.

Examples

Let vStartDate = Today();

Let vEndDate = Date(Today()+7);

Let vSales = ‘Sum({$<Date={“>=$(vStartDate)<=$(vEndDate)”}>} Sales)’

If you run the script, you will get ‘Sum({$<Date={“>=12/1/2012<=12/8/2012″}>} Sales)’ for the variable vSales.

If you don’t want to expand the Dollar sign expansion part, you need to separate above formula in three parts.

Let vSales2 = ‘Sum({$<Date={“>=$’ & ‘(vStartDate)<=$’ & ‘(vEndDate)”}>} Sales)’

You will get ‘Sum({$<Date={“>=$(vStartDate)<=$(vEndDate)”}>} Sales)’ for the variable vSales2.

If you are using single quotes in the formula, then you need to use chr(39).

Let vSales3 = ‘Num(Sum({$<Date={“>=$’ & ‘(vStartDate)<=$’ & ‘(vEndDate)”}>} Sales), ‘ & chr(39) & ‘##,##0′& chr(39) & ‘)’

You will get ‘Num(Sum({$<Date={“>=$(vStartDate)<=$(vEndDate)”}>} Sales), ‘##,##0′)’.

That’s it.

SET Vstring3=1+3;

let Vstring4=1+3;

set Vif=if(year=2006,year);

Let Vstring5=today();//expression evaluates

let Vstring6='today()';//store string if u use $(Vstring6) evaluates expression

set Vstring7=today(); //store string if u use $(Vstring7) evaluates expression

set Vstring8="=today()";//evaluates expression

let Vstring9='=today()';//evaluates expression

let Ver='if(year=2006,year,0)';//use string and evaluates expression also

Let Vstring8="=today()"; //it's value is null variable not created.

Re: Difference between LET and SET?

its_anandrjs — Thu, 08 May 2014 08:50:42 GMT

When you use SET

Set vVariableName = $ ; //It will treat as static strin which kept $ sign

and if you use

Let vToday = Num(Today()); // then this variable store today date as number and it will changed as today value change you can use

Let vNow = Num(Now());

Let vNow = Now();

and as James suggest

SET Add = '$1+$2';

LET vADD = $(Add(5,6)); //this gives 11

But if you use

SET vADD = $(Add(5,6)); //this gives '5+6'

Re: Difference between LET and SET?

Thu, 08 May 2014 09:03:42 GMT

Hi,

:SET :

The SET statement is used when you want a variable to hold the string or numeric value that is to the right of the Equal (=) sign.

When used, the variable is substituted by its value.

Variables can be used in the script for macro expansion and in various control statements.
This is very useful if the same string is repeated many times in the script, e.g. a path.

Set variable = string

Eg : Set Myvar=2*3 returns “ 2*3”

LET :

The let statement, in opposition to the set statement, evaluates the expression on the right side of the ' =' before it is assigned to the macro variable.

Let variable = Expression

Eg : Let MyVar =2*3 returns “6”

Hope it helps..

Bregards,

Reena Abraham

Re: Difference between LET and SET?

Thu, 08 May 2014 09:05:19 GMT

Thankyou