Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Set Analysis: get a pivot table

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

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Creator
Creator

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)
)

 

 

Best Regards,
Joseph Morales

View solution in original post

4 Replies
Highlighted
Creator II
Creator II

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))

Highlighted
Creator
Creator

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)
)

 

 

Best Regards,
Joseph Morales

View solution in original post

Highlighted
Contributor II
Contributor II

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 .

Highlighted
Contributor II
Contributor II

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