Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
D19PAL
Creator II
Creator II

Best way to do this group by

Hi,

I need to group each student to a teacher and then give me the highest level of that teacher

 

Teacher,Student,Level

1,1,H

1,2,L

1,3,M

2,4,M

2,5,L

3,7,H

4,8,M

5,9,L

6,10,H

7,11,M

7,12,L

7,13,H

 

So the results will be

H = 4

M = 2

L = 1

 

Thanks 

 

 

Labels (1)
5 Replies
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @D19PAL 

I don't follow how the output relates to the example data.

What you describe is:

1=H
2=M
3=H
4=M
5=L
6=H
7=H

But your example output doesn't show that.

Please confirm what you are wanting to achieve.

Steve

Angela_Zhou
Contributor III
Contributor III

First, add a number field called "LevelSortOrder" to assign sort number to Level as,
   1 to H
   2 to M
   3 to L
we can achieve it via Pick(Mach()) functions

tmpTable:
Load
     Teacher,
     Student,
     Level,
     PICK( Match(Level,'H','M','L') ,1 ,2 ,3) AS LevelSortOrder
...
;

Now, we can group by Teacher,
Then, sort student by "LevelSortOrder" and pick first/lowest number via FirstSortedValue() function

tblHighestLevel:
Load
     Teacher,
     FirstSortedValue(Student, LevelSortOrder) AS HighestStudent,
     FirstSortedValue(Level, LevelSortOrder) AS HighestLevel
Resident tmpTable
Group By Teacher;

//The table "tblHighestLevel" result should be,
Teacher, HighestStudent, HighestLevel
1, 1, H
2, 4, M
3, 7, H
4, 8, M
5, 9, L
6, 10, H
7, 13, H

//Then you will get your final results via UI chart/table based on data in table "tblHighestLevel"
Dimension: HighestLevel
Measure: Count(HighestStudent)

Angela Z.
D19PAL
Creator II
Creator II
Author

Hi,

Thanks for this, it works perfectly for the dummy data.

With my real data I have some students with more than one H, M or L and those are not adding up or showing.

Try the same with this modified data

Teacher,Student,Level

1,1,H

1,2,H

1,3,M

2,4,M

2,5,L

3,7,H

4,8,M

5,9,L

6,10,H

7,11,M

7,12,L

7,13,

 

Need the same results as before, I've modified student 2 to H.

 

Many thanks

 

 

MarcoWedel

Hi,

although I did not quite get the role of the students here, one solution might be:

MarcoWedel_1-1654103284693.png

using this dimension:

=Aggr(Mid('LMH',Max(Index('LMH',Level)),1),Teacher)


as well as this measure:

Count(DISTINCT Teacher)

 

hope this helps

Marco

Angela_Zhou
Contributor III
Contributor III

@D19PAL 

If you want to count multiple student with same highest Level under the same teach, then you can try:

// First, Build a temp table to hold combine keys need to keep: Each teach + Highest Level
tblKeysToKeep
:
Load
    Teacher &"|"&  FirstSortedValue(Level, LevelSortOrderAS HighestKey
Resident tmpTable
Group By Teacher;

//Then, load data with Where Exist condition to only keep records with matching combo Key
tblHighestLevel:
Load
     Teacher,
     Student,
     Level
Resident tmpTable-or-OriginalDataTable
Where Exist(HighestKey, Teacher &"|"& Level ) ;  //only keep Teach|Level = HighestKey

//The final table "tblHighestLevel" result based on your new data set should be,
Teacher, Student, Level
1, 1, H
1, 2, H
2, 4, M
3, 7, H
4, 8, M
5, 9, L
6, 10, H
7, 13, H

Angela Z.