Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
anwarbham
Contributor III
Contributor III

Loading Formulas from excel removes doller sign

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.

 

 

Labels (2)
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

6 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor III
Contributor III
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor III
Contributor III
Author

hope you had a good xmas below is the formula file 

 

zFormula Name%zFormula%zFormulaTextbox%zFormula Description%
vActualNum(sum({$<currency_conversion_type_id={1}>}[value_qty])
+
sum({$<currency_conversion_type_id={2}>}[value_qty])
/1000,'#,##0')
 Actual 
vActual_FYsum({$<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_PYsum({$<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_Periodicsum({$<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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor III
Contributor III
Author

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