Sub routines

[#1]

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;

  NEXT

  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));

  NEXT

  // 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

[Peter_Cammaert]

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

[phaneendra_kunc · Accepted solution]

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

  NEXT

  // 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

[jagan]

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

  NEXT

  // 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.