Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
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
Hi,
although I did not quite get the role of the students here, one solution might be:
using this dimension:
=Aggr(Mid('LMH',Max(Index('LMH',Level)),1),Teacher)
as well as this measure:
Count(DISTINCT Teacher)
hope this helps
Marco
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, LevelSortOrder) AS 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