Creating Variable through external file in Qlikview

Variable plays vital role in QlikView compared to other programming languages. Variable in QlikView is not just temporary storage while executing the script. Indeed, variables will exist after the script execution is finished.

Variables provide a lot of flexibility when you have to do calculations dynamically.

Variables can be used in data transformations and Calculations. It generally gets the value from LET, SET or other control statements through automated call or via input box in layout.

It can be created in script or UI level in QlikView. Here we are going to create a variable in script using the external file.

Let’s assume below is your content of external excel file,

Sample.xls

Name Expression Label
Budget sum(Amount) Total Budget
BudgetLastyear sum( {$<Year={“$(=max(Year)-1)”}>}  Amount) Previous year Budget
BudgetThisyear sum( {$<Year={“$(=max(Year))”}>}  Amount) Current year Budget

Load the table in qlikview script

TAB_Variable:

LOAD     Name,

Expression,

Label

FROM Sample.xls;

Use the following code to create variable

For i = 0 to NoOfRows(‘TAB_Variable ‘) – 1

// Name of the variable
let vName = peek (‘Name’, i, ‘TAB_Variable ‘);

// Expression
let $(vName) = peek (‘Expression’, i, ‘TAB_Variable’);
next i

Do the reload after finishing the script. You can check variable created by using Variable Overview in Settings or simply CTRL+ALT+V.

By: Sivaraj Seeman

Tags:
1 Comment
  • Karthik

    Nice article

    November 30, 2016

Leave a Comment

Your email address will not be published.