topic How to creat dynamic expressions - Fieldvalues to a new Field? in QlikView

How to creat dynamic expressions - Fieldvalues to a new Field?

Fri, 02 May 2014 14:36:48 GMT

Hello Guys,

My Name is Dennis from Germany, so sorry for my bad english

I try to generate a dynamic chart table. In the dimension-tab are the facts. e.g. sales, turnover, amount of sales and so on

The facts generated in a inline table in the script and looks like this:

LOAD * INLINE [

fact_ID, fact, fact_expression

|10|, Sales, var_sales

|20|, Turnover, var_turnover

|30|, Amount, var_amount

];

I try now to get the information out of the fact_expression field into a new field.

So the global expresion converts from sum(fact_expression) to each row and get the Informations as a new Field and sum() the original field.

Sales | sum(var_sales)

Turnover | sum(var_turnover)

My only successes were to change the one expression to sum($(=fact_expression)) -> But in a $(= ) QlikView lost the dimension-Row information and get all rows together in the fact_expression..

My only chance at the momemt is to ask him what expression i want with set expression (e.g. over the fact_ID)

=sum(

$(= only({<

Fakten_ID={"|10|"}

>}Fakten_Feld))

)

Or do select in a List box a fact or fact_id

But that displays me only one and not all facts.

-------------------

So the goal: i want only one expresion which converts my fieldvalues into a real field to aggregate on it over sum..

Hope you guys understand what my problem is..

Maybe QlikView cant solve this problem at the moment.

Re: How to creat dynamic expressions - Fieldvalues to a new Field?

swuehl — Fri, 02 May 2014 14:46:11 GMT

But you want 1 expression, that kind of switches based on the current dimension value?

Then you need to create something that will end up in an expression like

=pick( match(Dimension, 'Sales', 'Turnover', 'Amount')

,sum(Sales)

,sum(Turnover)

,sum(Amount)

)

You can create this expression in your script, there is an example in the forum how to do this, just can't find it right now.

edit:

found one:

How to make dynamic expressions work?

Re: How to creat dynamic expressions - Fieldvalues to a new Field?

jonathandienst — Fri, 02 May 2014 14:53:16 GMT

Dennis

Let me see if I understand you correctly - you would like to have a set of dynamic expressions calculated in a chart (or table) and you are trying to use set analysis to get the correct expression of each line based on a the expression number or name as a chart dimension?

This won't work I am afraid, as the set expression is evaluated once for the chart before the dimensions are created and has no concept of the dimension. The way to solve this problem is to construct a Pick(Match()) type of expression. So let's say that you have the field 'fact' as a dimension. Then you would need an expression like:

Pick(Match(fact_ID, '10', '20', '30'),

Sum(var_sales),

Sum(var_turnover),

Sum(var_amount)

)

If you have a few expressions, then this is quite easy to create. If there are many expressions (I had up to 50 in a recent document), then you will need to construct the Pick(Match()) into a variable and then use

$(vPickMatch)

to execute the variable.

HTH

Jonathan