Evaluate formulas inside Load script calculation

[aj0031724]

Team,

While evaluating the formula inside load script it is giving an error Ïnvalid expression"".

Is it possible to evaluate the  formulas inside load script for the attached qvw AND THE ATTACHED EXCEL.

[hic]

You use a variable called KPIFORMULAS:

   UTRANCELLKEY:

   LOAD *,

        $(KPIFORMULAS) AS RESULT

   FROM [...] ...;

but this variable does not exist. You have a field - from a previous data load - but this cannot be used this way. You need to assign a variable this value.

Further, if you do, it will still not work since your Sum() function needs a Group By clause in the Load.

HIC

[aj0031724]

Dear Henric,

Can you please help and  provide the correct ways of evaluating the formulas inside script?

[hic]

Let's say that you have an Excel sheet with two columns: Variable name and variable formula. Then you can use this in something similar to the following:

Variables:

LOAD VariableName, VariableValue

  FROM "File.xls" (biff, embedded labels, table is Sheet2$);

For vRowNo = 0 to NoOfRows('Variables')-1

  Let vVariableName = Peek('VariableName',vRowNo,'Variables');

  Let $(vVariableName) = Peek('VariableValue',vRowNo,'Variables');

Next vRowNo

Data:

Load *, $(xyz) as Field

  From "SourceData.xls" (...) ;

In other words: Load all variables into a table; then loop over this table and create a new variable in each loop. Finally you can use these variables (like the variable xyz in the example above) to define new fields in your load statements. But you cannot have formulas with aggregation functions (like Sum or Count), unless you have a "Group By" in your load statement.

You can also use these variables in the UI to define measures.

HIC