topic Re: Evaluate formulas inside Load script calculation in QlikView

Evaluate formulas inside Load script calculation

aj0031724 — Tue, 22 Sep 2015 08:51:10 GMT

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.

Re: Evaluate formulas inside Load script calculation

hic — Tue, 22 Sep 2015 11:51:41 GMT

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

Re: Evaluate formulas inside Load script calculation

aj0031724 — Thu, 24 Sep 2015 11:50:14 GMT

Dear Henric,

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

Re: Evaluate formulas inside Load script calculation

hic — Thu, 24 Sep 2015 13:42:29 GMT

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