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