topic Set Analysis: get a pivot table in QlikView

Set Analysis: get a pivot table

maverick1984_c — Sat, 16 Nov 2024 01:04:46 GMT

Hi, I have the following table

qualify *;

B:
LOAD * INLINE [
master_process_name,step_process_name, nb_run, step_exec_time
a,step1,1,10
a,step1,2,20
a,step2,1,30
a,step2,2,40
a,step2,3,5
];

I need to create a pivot table with 2 dimensions : "master_process_name" and "step_process_name".

In the pivot table, I need to create an expression that shows me the sum of execution time of all and only the process steps that have the maximum nb_run value inside the same step,
filtering out the all the other process steps.

The attached picture shows the goal I am trying to get. Which set analysis expression (or other solution) should I use?

Thank you , Best Regards

Re: Set Analysis: get a pivot table

bharathadde — Wed, 11 Mar 2020 18:35:46 GMT

Try this on Pivot Table

Note : Please check box "Always Fully Expanded" option on presentation tab for sure

Dimensions

master_process_name

step_process_name

nb_run

Expression

=if(nb_run=max(total <step_process_name> nb_run),Sum(step_exec_time))

Re: Set Analysis: get a pivot table

joseph_morales — Wed, 11 Mar 2020 19:19:04 GMT

Hi @maverick1984_c ,

Try with this in the expression:

sum( aggr( if(nb_run=max(total<step_process_name>nb_run), Sum(step_exec_time)) ,step_process_name,nb_run) )

Re: Set Analysis: get a pivot table

maverick1984_c — Wed, 11 Mar 2020 19:40:07 GMT

Hi Bharathadde , your solution is almost close to my goal, but it doesn't let to expand / close the pivot. Thank you anyway for the suggestion .

Re: Set Analysis: get a pivot table

maverick1984_c — Wed, 11 Mar 2020 19:42:03 GMT

Thank you Joseph Morales, this is exactly what I was searching for. TOP !!!! Best Regards