topic Re: Loading Formulas from excel removes doller sign in App Development

Loading Formulas from excel removes doller sign

anwarbham — Sat, 16 Nov 2024 03:45:14 GMT

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.

Re: Loading Formulas from excel removes doller sign

rwunderlich — Fri, 20 Dec 2019 15:11:30 GMT

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

Re: Loading Formulas from excel removes doller sign

anwarbham — Fri, 20 Dec 2019 15:29:15 GMT

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

Re: Loading Formulas from excel removes doller sign

rwunderlich — Fri, 20 Dec 2019 18:24:00 GMT

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

Re: Loading Formulas from excel removes doller sign

anwarbham — Thu, 26 Dec 2019 08:38:28 GMT

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

Re: Loading Formulas from excel removes doller sign

rwunderlich — Fri, 27 Dec 2019 16:13:19 GMT

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

Re: Loading Formulas from excel removes doller sign

anwarbham — Fri, 27 Dec 2019 16:47:16 GMT

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