Dollar Sign Expansion in QlikView (Latest) – Complete Guide

In QlikView scripting, there are many features that make creating a script comparatively convenient. One such feature is QlikView Dollar sign expansions.

This tutorial dedicatedly discusses what are dollar sign expansions in QlikView and what are they used for in QlikView. Moreover, we will learn how to use dollar sign expansions to expand variables, parameters and expressions

So, let’s start exploring Dollar Sign Expression in QlikView.

What is Dollar Sign Expansion in QlikView?

QlikView Dollar sign expansion is a method of expanding or replacing values (string or numbers) in a script.

We first define a variable, parameter or an expression in the script and then use dollar-signs to create a container like which defines what intends to fill this space.

During script execution, the variable, expression, the parameter having a dollar sign is read and replace by the intended or actual value (relevant to the data).

The values written as dollar-signs follow a syntax,

$(variable|expression|parameter)

Let us understand it better through an example. Say we have a line of command from the script.

If(Year=$(=Year(Today())), LightGreen())

Here, the QlikView dollar sign expression or function is Year(Today()) which will evaluate during script execution and this value will replace by the resultant value of the evaluation.

If the year in today’s date is 2018, then Year(Today())  will replace by 2018. The expression after the dollar sign expression gets evaluate will be,

If(Year=2018, LightGreen())

This replacing of values represents by a dollar sign are said to expand. The term expansion does not mean expanding a value in size, but it means revealing or unfolding the actual value which is to fill in place of the dollar-signed value.

Hence, the name Dollar sign expansion in QliKview, as the expansion is done using the dollar-signs as indicators pointing towards the values that need to expand.

QlikView Dollar sign expansion can contain or can use with a variable, parameter or expression. In the following sections, we will understand each type in detail.

Dollar Sign Expansion Using a Variable

Variables are expanded to the values they suppose to contain when evaluate. The QlikView dollar-sign expansion which is done on variables use syntax as given below,

• For text variables

$(variablename)     

• For numeric variables expansion

$(#variablename)      

In case of text variables if the variablename does not equate to any value then an empty string returns. Similarly, in the case of numeric variables, if variablename not assign to a value then 0 returns.

Let us understand how variables are expanded with QlikView dollar sign expansion method.

Example 1

SET DecimalSep=’,’;

LET X = 9/2;

Dollar-sign expansion $(X) will expand to 4,5 (because it gives string values), while $(#X) will expand to 4.5 (as numeric values are given as a result).

Example 2

Set Mypath=C:\MyDocs\Files\;

…

LOAD * from $(MyPath)abc.csv;

Data will be loaded from C:\MyDocs\Files\abc.csv.

Example 3

Set CurrentYear=2015;

…

SQL SELECT * FROM table1 WHERE Year=$(CurrentYear);

Rows with Year=2015 will be selected.

Dollar Sign Expansion Using Parameters

Parameters can be set using the dollar sign expansion in QlikView. The parameters must define as $1 $2 $3, as many you like.

The values expanding as these parameters must have a comma and separate only be as much in number as defined in the dollar-sign expression.

Whether you want the parameters to treat as a string or as numeric values is decided by keywords Set (for strings) and Let (for numbers).

Example

Set SUM=’$1+$2+$3’;

Set X=$(SUM(2,4,6)); // will return a string ‘2+4+6’ as the value of X

Set SUM=’$1+$2+$3’;

Let X=$(SUM(2,4,6)); // will return the sum of three values 12 in X

Now for instance, if we define a variable SUM with some parameters,

Set SUM=’$1+$2+$3’; // known as formal parameter

Set X=$(SUM(2,4,6)); // known as actual parameter

Here both the numbers of formal ($1, $2, $3) and actual (2,4,6) parameters are equal hence making it an ideal situation.

But, however, if the formal and actual parameters are not equal and one exceeds the other, only the values in the actual parameter that corresponds of values in the formal parameter will use, ignoring others.

Hence, it is important to follow what define in the formal parameter.

Pay attention to the example cases given below explaining the point made about formal parameters.

Example:  

Set MUL=’$1*$2’;

Set X=$(MUL); // returns ‘$1*$2’ in X

Set X=$(MUL(12)); // returns ’12*$2′ in X

Let X=$(MUL(5,7,9)); // returns 35 in X, ignoring the third value 9

because it exceeds the formal parameter value

limit.

Dollar Sign Expansion Using an Expression

Expressions of a script can also expand using the dollar sign expansion in QlikView. The syntax for expressions is,

$(=expression )

The expression will evaluate and return a value which the expression will replace by. For example,

$(=Year(Today())); // returns a string with the current year i.e. 2018.

$(=Only(Year)-1); // returns the year before the selected one i.e. 2017.

File Inclusion Using Dollar Sign Expansion

Files from the system i.e. external files can also include in the script using dollar sign expansion in QlikView. In this case, the file name will expand to the file contents.

This option is of great use when you want to store script or part of a script as a text file, you can do it using dollar sign expansion so that the script file expands to the original script commands during script execution.

The syntax used for file inclusion is,

$(include=filename)

Example:  

$(include=C:\Documents\MyScript.qvs);

So, this was all about QlikView Dollar Sign Expansion Tutorial. Hope you like our explanation.

Conclusion

Hence, we discussed what is dollar sign expansion in QlikView, then we understood the ways in which dollar sign expansions can use.

We saw how to use dollar sign expansions to expand variables, parameters and expressions. We hope this tutorial was helpful.

In case of any queries, drop it in the comment box below, we will be happy to help.