topic Re: Loading variables to a straight table based on dimension and expression fields in QlikView

Loading variables to a straight table based on dimension and expression fields

Thu, 03 Apr 2014 11:47:10 GMT

Hi, wondering if anyone has some smart ideas about how to solve this

Is it possible to define a variable based on its position in a table, then return the value for that variable?

See the example below:

Dim	1	2	3
Sales	$(varSales1)	$(varSales2)	$(varSales3)
Volume	$(varVolume1)	$(varVolume2)	$(varVolume3)
Margin	$(varMargin1)	$(varMargin2)	$(varMargin3)
...	...	...	...

Each of the variables contain Set Analysis, and I can get each one individually to display in a text box, then build a "table" structure by moving the boxes around.

But it would be cleaner if this was one straight table, easier to export, etc.

What I would like to do is set an expression that essentially "concatenates" the column and row names into a variable name?

E.g. Column = (calculation) "1", Dimesnion = "Sales", Variable = $(varSales1)

Then the result displayed is the result calculation of the variable e.g. "=$(varSales1)"

Any ideas if this is possible?

Alternatively I could create this as a "Flat Table" in the load script and make the calculations there, but doing it this way would be much cooler!

Re: Loading variables to a straight table based on dimension and expression fields

its_anandrjs — Thu, 03 Apr 2014 11:54:30 GMT

Use a straight table for this

Eg:-

In dimension write

'Sales' and in expression followed by variables like $(varSales1) ,$(varSales2) ,$(varSales3)

same for

'Volume' and in expression $(varVolume1),$(varVolume2),$(varVolume3)

same for

'Margin' and expression $(varMargin1),$(varMargin2),$(varMargin3)

means create three straight tables

New Update:- And remove its caption and title from the chart and arrange tables in sequence Sales,Volume,Margin

Re: Loading variables to a straight table based on dimension and expression fields

jonathandienst — Thu, 03 Apr 2014 12:06:23 GMT

You can do this. I am assuming that 1, 2, 3 along the top are 3 expressions. Define expression 1 like:

=Pick(Match(Dim, 'Sales', 'Volume', 'Margin'), $(varSales1), $(varVolume1), $(varMargin1))

This will apply the correct expression. The variables must contain legal QV code, without a preceding = sign, so that they will be expanded into the Pick(Match()) expression.

Do the same thing with expressions 2 and 3, just change the index number in the variable names.

HTH

Jonathan

Re: Loading variables to a straight table based on dimension and expression fields

jonathandienst — Thu, 03 Apr 2014 12:12:43 GMT

To add to that post: rather than Pick(Match(....)), you can use Pick(RowNo(), ...).

If you have many values of Dim, so the pick match (or Pick(RowNo()..) expression gets very long and awkward to manually capture and maintain, you can use the load script to construct the Pick() expression. I have done that with about 50 sub-expressions (read from a spreadsheet).

But be warned that this can be a performance killer - in my experience there is quite a calculation overhead with large Pick(Match()) expressions, so you would need to test the performance of your application. This should not be a problem with less than 10 expressions, although that depends on the complexity of the expressions in the variables.

Jonathan

Re: Loading variables to a straight table based on dimension and expression fields

its_anandrjs — Thu, 03 Apr 2014 12:13:00 GMT

You can use any inline table also like

Dimension:

Load * inline

[

Dim_Name

Sales

Volume

Margin

];

and in chart use Dimenion Dim_Name and in expression variable followed by Dimension name.

Re: Loading variables to a straight table based on dimension and expression fields

telepuzik — Thu, 03 Apr 2014 12:16:57 GMT

Pase fnd the attached on how to acheive your rquest.

Re: Loading variables to a straight table based on dimension and expression fields

Thu, 03 Apr 2014 12:19:04 GMT

Thanks Jonathan, Works like a dream.

Eventually the list will get quite long which might make the code unmanageable, but looks good so far!

Re: Loading variables to a straight table based on dimension and expression fields

Thu, 03 Apr 2014 12:24:27 GMT

Thanks.

This does what I wanted to do.

I think the "Pick" function suggested by Jonathan is cleaner than nested 'if' statements and probably better for performance as well though