topic Sub routines in QlikView

Sub routines

Tue, 14 Jul 2015 10:02:28 GMT

HI All,

Can anyone please explain me the below code.

SUB LoadVars

FOR Each I in '[TimeFrames$]','[Paths$]', '[Variables$]','[Expressions$]','[Colors$]','[MileStone Slab$]'

_Variables:

ADD LOAD

[Variable Name],

if(Calculated='Y','='&Value,Value) as Value,

Calculated

FROM [..\..\00_includes\config\Config.xls] (biff, embedded labels, table is $(I))

Where len([Variable Name]) > 0;

FOR I=1 TO NoOfRows('_Variables')

LET VarName = Peek('Variable Name', I-1, 'Variables');

LET $(VarName) = Replace(Peek('Value', I-1, 'Variables'),'@',"'");

TRACE $(VarName)=$($(VarName));

// Cleanup

Drop Table _Variables;

SET VarName=;

SET I=;

FieldMap:

Mapping LOAD Source,

Target

FROM

[..\..\00_includes\config\Config.xls]

(biff, embedded labels, table is [Fields$]);

RENAME FIELDS USING FieldMap;

END SUB

Re: Sub routines

Peter_Cammaert — Tue, 14 Jul 2015 10:08:20 GMT

This script subroutine does two things:

From a series of sheets in config.xls, variable definitions are loaded. The complexity of the code has to do with the fact that variables cannot end up in a table (like in the first loop), they must be individually defined. That is done in the second loop which also substitutes @-placeholders for quotes (which cause trouble in Excel).
Then from the Fields$ sheet in config.xls, the subroutine loads field name translations. This means that the sheet has two columns (Source = original (bad) field name, Target = better (nice) field name). The RENAME statement will rename all existing fields that appear in this sheet.

Peter

Re: Sub routines

phaneendra_kunc — Tue, 14 Jul 2015 10:16:16 GMT

Seems like you have buch of sheets in you config excel and looping through all sheets and creating variables. find my comments in bold

SUB LoadVars

FOR Each I in '[TimeFrames$]','[Paths$]', '[Variables$]','[Expressions$]','[Colors$]','[MileStone Slab$]' //---Excel sheets

_Variables:

ADD LOAD

[Variable Name],

if(Calculated='Y','='&Value,Value) as Value,

Calculated

FROM [..\..\00_includes\config\Config.xls] (biff, embedded labels, table is $(I)) //----$(I) will pick each sheet name

Where len([Variable Name]) > 0;

NEXT //--after finishing each sheet it goes to next one in the list above until all of them are read.

FOR I=1 TO NoOfRows('_Variables') //once it read all the sheets from the above logic, it will save all the values in the "Variables table" above. Now this code is looping through each row in this table and creating a variable.

Example : vSales = sum(sales)

Here vSales -- Variable Name and sum(Sales) -- is Value

LET VarName = Peek('Variable Name', I-1, 'Variables'); //Not sure if this is working. Your table name is _Variables and here it is looking for Variables. This will out pt null.

LET $(VarName) = Replace(Peek('Value', I-1, 'Variables'),'@',"'");

TRACE $(VarName)=$($(VarName)); // It is just to trace the out put in the reload console

// Cleanup

Drop Table _Variables;

SET VarName=;

SET I=;

FieldMap: //A maping load

Mapping LOAD Source,

Target

FROM

[..\..\00_includes\config\Config.xls]

(biff, embedded labels, table is [Fields$]);

RENAME FIELDS USING FieldMap; this code will rename fields as per your business and they are defined in "Fields" sheet in you excel file.

END SUB

Re: Sub routines

jagan — Tue, 14 Jul 2015 10:26:43 GMT

Hi,

Check this

SUB LoadVars

FOR Each I in '[TimeFrames$]','[Paths$]', '[Variables$]','[Expressions$]','[Colors$]','[MileStone Slab$]' // Loop through the Names of Excel sheets to Load

_Variables: // Storing all the variables in the Excel sheet to Qlikview Table

ADD LOAD // ADD Partial Reload

[Variable Name],

if(Calculated='Y','='&Value,Value) as Value, // If whether the value to be calculated, if yes = is concatenated to the value

Calculated

FROM [..\..\00_includes\config\Config.xls] (biff, embedded labels, table is $(I)) // $(I) Replaces the Sheet name and loads the data in the sheet of the Excel file

Where len([Variable Name]) > 0;

NEXT // Load Next Sheet

FOR I=1 TO NoOfRows('_Variables') // Creating the variables dynamically with the values read from the excel file

LET VarName = Peek('Variable Name', I-1, 'Variables');

LET $(VarName) = Replace(Peek('Value', I-1, 'Variables'),'@',"'"); // @ symbol is replacing with Single Quotes

TRACE $(VarName)=$($(VarName)); // Logging the read value

// Cleanup

Drop Table _Variables;

SET VarName=;

SET I=;

FieldMap:

Mapping LOAD Source, // Loading the field names from Excel file

Target

FROM

[..\..\00_includes\config\Config.xls]

(biff, embedded labels, table is [Fields$]);

RENAME FIELDS USING FieldMap; // Renaming the Qlikview field names read from the Excel file

END SUB

Hope this helps you.

Regards,

Jagan.