Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
@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 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:
Thank you Taoufiq! This really helps 🙂