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

[nora7]

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!

[Taoufiq_Zarra · Accepted solution]

@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:

[nora7]

Thank you Taoufiq! This really helps 🙂