topic Re: Expressions in variables in QlikView

Expressions in variables

Tue, 05 Nov 2013 14:00:32 GMT

Hi!

I'm trying to store QV expressions in variables using Excel and a loop in the QV-script that looks like this:

for i = 0 to NoOfRows('expressions') - 1

let vName = peek('variablename', i, 'expressions'); // Name of the variable

let $(vName) = peek('Formula', i, 'expressions'); // Expression

next i

And this works perfectly. It generates a number of variables containing expressions. The problem I have is that the variables I have are supposed to be in the same expression in QlikView.

So lets say that I have a variable called 'v1', to calculate this my expression would be $(v1) and it will execute the expression inside the variable.

But how do I do this for all my variables? Every variable is connected to a dimension in the table. So if I create a new variable which just says 'variablename' and put this in an expression in my table, my table will look like this:

So I get my name of the right variable but I can't calculate the right variable.

Hope you understand my problem. Any suggestions on what to do?

Re: Expressions in variables

jonathandienst — Tue, 05 Nov 2013 14:14:17 GMT

Not sure is this is your problem, but have you tried

$($(v1)) or $(=$(v1))

HTH

Jonathan

Re: Expressions in variables

Tue, 05 Nov 2013 14:28:55 GMT

Did not help I'm afraid. I get it to work if I create a new variable that looks like this:

if(variabelname='v1',$(v1),

if(variabelname='v2',$(v2),

if(variabelname='v3',$(v3)))

And then use this variable as an expression.

Re: Expressions in variables

swuehl — Tue, 05 Nov 2013 14:29:14 GMT

Kim, as far as I understand you want QV to evaluate a different expression for every line of your table.

I believe this is not possible. The expression is set for all dimension values.

You can look into adding a pick(match() ) combination to your expression, i.e. creating one expression that caters for all your lines. I remeber one or two samples here in the forum that tried to handle mostly exactely what you are trying, but don't have much time to search for it now.

Re: Expressions in variables

Tue, 05 Nov 2013 14:42:25 GMT

Thanks for that. I will try to search for a similar solution. If anyone else has seen it, please let me know

Re: Expressions in variables

swuehl — Tue, 05 Nov 2013 14:46:04 GMT

It's in principle similar to what you found out by yourself:

=if(variabelname='v1',$(v1),

if(variabelname='v2',$(v2),

if(variabelname='v3',$(v3)))

can be also written as

=pick(match(variabelname,'v1','v2','v3', $(v1), $(v2), $(v3) )

And you can try to generate this expression automatically using the available variablename values and variables.

Re: Expressions in variables

Tue, 05 Nov 2013 14:53:13 GMT

Thanks for that!

So, if I have lets say 3 expressions (v1-v3) containing expressions, and want these calculated in one expression in a table. How to I write the expression? Do I have to create an extra variable?

Re: Expressions in variables

swuehl — Tue, 05 Nov 2013 15:00:21 GMT

I just found one of the threads mentioned, hope this clarifies most things:

Dollar sign expansions removes dimensions

Re: Expressions in variables

Tue, 05 Nov 2013 15:28:12 GMT

Thanks, I got it to work now!

The problem for me is that I have about 90 variables with expressions which makes the calculation take a long time. Is there any faster way to calculate the right variable for the right row in a table?

Re: Expressions in variables

Wed, 06 Nov 2013 15:24:50 GMT

So now I've managed to make the following expression:

pick(index,$(variable1),$(variable2)...$(variable99))

Where index is the right number for the right variable.

But it's still to slow, which is really not that strange. Is there anyway to make this calculation faster?

Re: Expressions in variables

swuehl — Wed, 06 Nov 2013 15:28:03 GMT

I think you need to look at your 99 expressions and optimize those (or at least the ones that create the heavy load).