topic How can we summarize columns in a table for each row value, and rank the columns based these sums? in QlikView

How can we summarize columns in a table for each row value, and rank the columns based these sums?

nora7 — Fri, 12 Mar 2021 11:36:19 GMT

I have data in the form:

Name	X	Y	Z
A	1	0	2
A	2	0	5
B	0	5	1
C	1	4	0
C	2	5	1
C	2	4	0

Is there any way that I can create a table showing distinct names A,B,C and the highest 2 among sum of X,Y,Z?

Name	Highest 2	2 Highest values
A	Z,X	7,3
B	Y,Z	5,1
C	Y,X	13,5

I was guessing maybe getting the data to this form would help, but I am not able to figure how to get this too: (summarize the columns for each name)

	Sum(X)	Sum(Y)	Sum(Z)
A	3	0	7
B	0	5	1
C	5	13	1

I am very new to QlikView so I am not very aware of all the functions that QlikView offers. It would be great if someone could help me with this!

Re: How can we summarize columns in a table for each row value, and rank the columns based these sums?

Taoufiq_Zarra — Fri, 12 Mar 2021 15:08:10 GMT

@nora7 Maye be like this :

Data: LOAD * INLINE [ Name, X, Y, Z A, 1, 0, 2 A, 2, 0, 5 B, 0, 5, 1 C, 1, 4, 0 C, 2, 5, 1 C, 2, 4, 0 ]; Tmp: noconcatenate load Name,sum(X) as X,sum(Z) as Z,sum(Y) as Y resident Data group by Name order by Name; drop table Data; Tmp2: CrossTable(Sum, Data) load * resident Tmp; drop table Tmp; output: noconcatenate load Name,FirstSortedValue(Sum,-Data,1)&','&FirstSortedValue(Sum,-Data,2) as [Highest 2],Max(Data,1)&','&Max(Data,2) as [2 Highest values] resident Tmp2 group by Name; drop table Tmp2;

output:

Re: How can we summarize columns in a table for each row value, and rank the columns based these sums?

nora7 — Mon, 15 Mar 2021 06:28:24 GMT

Thank you Taoufiq! This really helps 🙂