Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
This script subroutine does two things:
Peter
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
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.