Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
nora7
Contributor II
Contributor II

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

I have data in the form:

 NameXYZ
A102
A205
B051
C140
C251
C240

 

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?

NameHighest 22 Highest values
AZ,X7,3
BY,Z5,1
CY,X13,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)
A307
B051
C5131

 

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!

Labels (4)
1 Solution

Accepted Solutions
Taoufiq_Zarra

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

Taoufiq_Zarra_0-1615561676982.png

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

2 Replies
Taoufiq_Zarra

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

Taoufiq_Zarra_0-1615561676982.png

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
nora7
Contributor II
Contributor II
Author

Thank you Taoufiq! This really helps 🙂