topic Re: Bulding a Pivot Table with an expression that satisfy a condition such as 'Column = max(Column)' in QlikView

Bulding a Pivot Table with an expression that satisfy a condition such as 'Column = max(Column)'

Mon, 12 Nov 2012 10:00:40 GMT

Hi,

I've got a table like this :

Project_Id Scenario_Id Value

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

1000 1 12

1000 2 13

1000 3 14

2000 1 12

2000 2 15

I woul like to build a pivot table that returns this :

Project_Id (Dimensions) Value(Expression)

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

1000 14

2000 15

where Value(Expression) = sum( if( Scenario_Id = Max(Scenario_Id in the contexte of the dimension Projetc_Id), Value ) )

But It does not work.

I tried to place a calculated dimension like aggr( max(Scenario_Id), Project_Id) a then an expression like sum( Value ) but my expression is not evaluated in the context of this new dimension.

What can I do ?

Is it possible to solve my request in QlikView ?

With many thanks,

Oomingmak.

Re: Bulding a Pivot Table with an expression that satisfy a condition such as 'Column = max(Column)'

MayilVahanan — Mon, 12 Nov 2012 10:03:40 GMT

Try with

firstsortedvalue() function

Hope it helps

Re: Bulding a Pivot Table with an expression that satisfy a condition such as 'Column = max(Column)'

Anonymous — Mon, 12 Nov 2012 10:06:59 GMT

hi Jean

PFA......i have updated the app...........

Re: Bulding a Pivot Table with an expression that satisfy a condition such as 'Column = max(Column)'

Mon, 12 Nov 2012 11:59:23 GMT

Than you for your answer.

The Max(Scenario_Id) cannot be loaded in script because It depends on a variable (a date) filled by the user.

Sorry I did not precise.

The max must necessary be dynamicly be evaluated in an expression.

Oomingmak.

Re: Bulding a Pivot Table with an expression that satisfy a condition such as 'Column = max(Column)'

Mon, 12 Nov 2012 12:16:28 GMT

I am not sure to use this correctly.

I tried FirstSortedValue( Scénario_Id, -Scenario_Id ) but it returns Null.

Could you help me ?

Re: Bulding a Pivot Table with an expression that satisfy a condition such as 'Column = max(Column)'

Gysbert_Wassenaar — Mon, 12 Nov 2012 12:57:28 GMT

You want to get the Value corresponding to the highest Scenario_ID per Project_ID. So try:

FirstSortedValue(Value,-Scenario_Id)

See attached qvw for how the firstsortedvalue function works for your example.

Re: Bulding a Pivot Table with an expression that satisfy a condition such as 'Column = max(Column)'

Mon, 12 Nov 2012 13:31:38 GMT

OK, thank you for your example.

I simplified my example to post it on QlikCommunity.

In my table, I've got multiple occurences of the same Project_Id and Scenario_Id and I would like to sum the values of these occurences.

In my example, if I Have

1000 1 10

1000 2 20

1000 3 30

1000 3 40

I would like to obtain :

1000 70 (= 30 +40)

Expression like FirstSortedValue(Sum(Value),-Scenario_Id) does not work

Do you think I can still use FirstSortedValue in ma case ?

Re: Bulding a Pivot Table with an expression that satisfy a condition such as 'Column = max(Column)'

Mon, 12 Nov 2012 14:42:52 GMT

I did not understood what is your actual issue......if you want to have max value for the project_id you can use two functions

LastValue(Value)

MaxString(Value)

Thanks

Re: Bulding a Pivot Table with an expression that satisfy a condition such as 'Column = max(Column)'

Mon, 12 Nov 2012 16:29:49 GMT

Use a nested rank function similar to

=IF(AGGR(RANK(AGGR(MAX(Scenario_ID),Project_ID),4),Project_ID)=1,Project_ID,NULL())

Re: Bulding a Pivot Table with an expression that satisfy a condition such as 'Column = max(Column)'

Gysbert_Wassenaar — Mon, 12 Nov 2012 19:31:44 GMT

See attached updated qvw

Re: Bulding a Pivot Table with an expression that satisfy a condition such as 'Column = max(Column)'

Tue, 13 Nov 2012 09:51:46 GMT

Thank you,

This is the fix for my problem.