topic external expressions in QlikView

external expressions

xtrimf — Thu, 24 Sep 2009 20:56:41 GMT

Hi,

My customer wants to maintain an expression list in an excel file.

QV treat these expressions as regular text and it is not possible to evaluate text in a chart.

Variables is also not best solution because there are hundreds of expressions.

Is there a way to use these expression once loaded into QV besides assigning it to variables?

external expressions

Thu, 24 Sep 2009 21:49:40 GMT

Do you mean that these expressions are fixed at load time or dynamically recalculated each time?

You can use the "evaluate ( <string> )" function, but not in chart expressions. This would be done in the load script.

If you use "LET varString = 'a + b' " , the variable will hold the value of a + b at the time this line is encountered, and variable expansion will give that value.

If you use "SET varString = 'a + b' " instead of LET, the variable will hold the string, and variable expansion will give the text, which is then calculated using the values of a and b when that calculation takes place.

This is probably not what you meant, but I am not sure that I understood.

external expressions

Thu, 24 Sep 2009 22:06:57 GMT

Hi,

please see the attached example.

Use the DEBUGGER to see what happens if you reload the script.

Good luck!

Rainer

external expressions

xtrimf — Sat, 26 Sep 2009 04:05:12 GMT

Hi,

The reason we are trying to do it in excel is because we need a different expression for each row.

with the current solution I still have to do :

if(rowno()=1,$(Variable1))

if(rowno()=2,$(Variable2))

This is what we are trying to avoid because of performance issues.

I thought that by doing it in excel i could just load the same field...

no solution yet...

external expressions

Oleg_Troyansky — Sat, 26 Sep 2009 23:07:25 GMT

xtrim,

couple thoughts:

1. if you could describe your business problem, perhaps we could find a better solution. Using a different expression in each row sounds ... hm, a little extreme - there has to be a better way.

2. If you really need to do it the way you describe, it's not such a big deal to assign hundreds of expression into variables automatically:

- read from excel

- process the data row by row. Process the following in the loop:

- assign variables holding the name of the "expression variable" and the formula

- assign the formula to the desired variable name, like this:

LET $(v_VarName) = $(v_Formula);

cheers!

external expressions

xtrimf — Tue, 29 Sep 2009 18:17:10 GMT

Well,

I'll try to describe the problem:

* - I have a budget table and a fact table. the tables cannot be linked (except time dimention).

* - the budget has also "Total" Sections.

* - The customer has many reports, each assembled from different budget rows.

* - For each budget row there are several calculation ( Budget, Actual, Diff, % Diff)

* The "Actual" calculation can be very complex sometime, while the budget calcs are quit simple.

* For each budget section there is a different expression for each { Budget, Actual, Diff, % Diff}

I tried the following:

1 - budget row names as dimentions, and calculation ( Budget, Actual, Diff, % Diff) as expressions.

This solution requires each calc to identify the row that it is in and apply the correct calculation for this row.

major performance issue in this one.

2 - budget row name as expressions and ( Budget, Actual, Diff, % Diff) as dimensions.

This solution requires that for each budget row I need to identify the dimension and apply the correct calc.

I hope this is clear.

external expressions

Thu, 11 Mar 2010 14:59:18 GMT

I have more or less the same problem. Oleg's solution for loading external expressions into QlikView seems to be working fine. However, I am having some with how to reference the expression.

Each different item I load has a different formula. In the load script I assign these formulas to a variable named v_[Name of item], so v_Sales, v_Visits, v_Costs etc.

I want to display these items in a straight table, I know the name of the item so I want to reference the variable with something like this:

$($(='v_' & ItemName)

Unfortunately, this doesn't seem to work. How can I dynamically change the name of the variable that is being referenced?