Loading Formulas from excel removes doller sign

[anwarbham]

hi 

im loading formulas as variables in qlik sense from an excel file 

example  i have the below in an excel cell

sum({$<version_code={10},currency_conversion_type_id={1,2},time_calculation_code = {'YTD'}, currency_code = {'EUR'},profit_center_code =- {1000}, adjust_code =- {'non-operating items'}, MonthNumber = {$(=num(Month(Today())))}>}[value_qty])/1000

but for some reason after the import when i go to use the variable ie $(myvar)

Qlik removes the trailing Doller sign 

so MonthNumber = {$(=num(Month(Today())))}

becomes MonthNumber = {}

how can i resolve this . ive tried replace function to substitute $ with 
! then at runtime replace ! with $ but still no success.

any help will be appreaciated.

[rwunderlich]

Your loading script is incorrect.  You are probably using LET/SET to create the variable whereas you should be using Peek(). Something like this:

// First load the variables from an external source, in this example, an xls file.
Variables:
LOAD Varname,
     Varvalue
FROM
Variables_Input.xls
(biff, embedded labels, table is MyVars1$)
;
// Process each row of the Variables table
FOR i = 0 to NoOfRows('Variables')-1
LET vVarname = peek('Varname',$(i),'Variables');
LET $(vVarname)=peek('Varvalue',$(i),'Variables');
NEXT i
// Clean up temp script vars
SET i=;
SET vVarname=;

DROP TABLE Variables; // No longer need table

Please post your script if you have further questions.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[anwarbham]

Thanks rob for the quick reply ,

my loading script is below

 
%Formulas:
LOAD
 [zFormula Name%],
 zFormula%,
 zFormulaTextbox%,
 [zFormula Description%]
FROM
[lib://$(vLocalData_Common)/Formulas.xls]
 (biff, embedded labels, table is Formulas$)
;

// Setup variable with number of formulas
Let vNoRows = noofrows('%Formulas');
// Loop over all rows and setup a variable per formula
For I = 0 to $(vNoRows) - 1
 Let vVarName = peek('zFormula Name%', $(I), '%Formulas');
 Let vVarValu = peek('zFormula%', $(I), '%Formulas');
 Let vVarValuTextbox = peek('zFormulaTextbox%', $(I), '%Formulas');
 Set $(vVarName) = "$(vVarValu)";
 Set $(vVarName)_Textbox = "$(vVarValuTextbox)";
Next
// Set all unnecessary variable to null()
Let vVarName = null();
Let vVarValu = null();
Let vNoRows  = null();
Let I = null();

[rwunderlich]

This is where things are going wrong:

Set $(vVarName) = "$(vVarValu)";
 Set $(vVarName)_Textbox = "$(vVarValuTextbox)";

You should only use Peek() to LET the variables.  There seems to be some redundant work going on in your code as well. Can you post a few lines of the variables file to help us understand what the intent is?

-Rob

[anwarbham]

hope you had a good xmas below is the formula file 

zFormula Name%	zFormula%	zFormulaTextbox%	zFormula Description%
vActual	Num(sum({$<currency_conversion_type_id={1}>}[value_qty]) + sum({$<currency_conversion_type_id={2}>}[value_qty]) /1000,'#,##0')		Actual
vActual_FY	sum({$<version_code={10},currency_conversion_type_id={1,2},time_calculation_code = {'YTD'},currency_code = {'EUR'},profit_center_code =- {1000}, adjust_code =- {'non-operating items'}, MonthNumber ={$(=max(MonthNumber))} >}[value_qty])/1000
vActual_FY_PY	sum({$<version_code={10},currency_conversion_type_id={1,2},time_calculation_code = {'YTD'}, currency_code = {'EUR'},profit_center_code =- {1000}, adjust_code =- {'non-operating items'},Year = {$(=Year-1)}, MonthNumber ={$(=max(MonthNumber))} >}[value_qty])/1000
vActual_Periodic	sum({$<version_code={10},currency_conversion_type_id={1,2},time_calculation_code = {'MONTH'}, currency_code = {'EUR'},profit_center_code =- {1000}, adjust_code =- {'non-operating items'}>}[value_qty])/1000

[rwunderlich · Accepted solution]

Use the script as I showed above and modify the LET statements to use your column names:

LET vVarname = peek('zFormulaName%',$(i),'%Formulas');
LET $(vVarname)=peek('zFormula%',$(i),'%Formulas');

LET $(vVarname)_Textbox =peek('zFormulaTextbox%',$(i),'%Formulas');

[anwarbham]

Thanks that has worked now the formulas work from excel with multiple $ signs