Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all, below is my sample table:
ID Career Strm GPA
1 | M | 1530 | 0.000 |
1 | M | 1610 | 5.000 |
1 | M | 1620 | 4.750 |
1 | U | 1220 | 0.000 |
1 | U | 1230 | 4.000 |
1 | U | 1310 | 3.860 |
1 | U | 1320 | 3.860 |
2 | P | 1730 | 3.500 |
2 | P | 1810 | 3.830 |
2 | P | 1820 | 3.830 |
2 | P | 1830 | NULL |
2 | U | 1220 | 0.000 |
2 | U | 1230 | 4.500 |
2 | U | 1310 | 4.570 |
2 | U | 1320 | 4.570 |
For each ID, I want to select the GPA of the earliest STRM, grouped by the Career where GPA = '0'. Next, I would like to count the number of IDs.
Desired output:
ID Career Strm GPA
1 | M | 1530 | 0.000 |
1 | U | 1220 | 0.000 |
2 | U | 1220 | 0.000 |
Total count = 3
I tried =FirstSortedValue(GPA,aggr(ID,[Strm],[Career])) as the GPA dimension but it did not work. Any ideas?
Thank you so much in advance.
You could do it like this:
LOAD
ID,
Career,
Min(Strm) AS Strm,
0 AS GPA
INLINE [
ID Career Strm GPA
1 M 1530 0.000
1 M 1610 5.000
1 M 1620 4.750
1 U 1220 0.000
1 U 1230 4.000
1 U 1310 3.860
1 U 1320 3.860
2 P 1730 3.500
2 P 1810 3.830
2 P 1820 3.830
2 P 1830 NULL
2 U 1220 0.000
2 U 1230 4.500
2 U 1310 4.570
2 U 1320 4.570
] (delimiter is spaces)
WHERE
GPA=0
GROUP BY
ID,Career;
Data:
load * Inline
[
ID,Career,Strm,GPA
1,M,1530,0.000
1,M,1610,5.000
1,M,1620,4.750
1,U,1220,0.000
1,U,1230,4.000
1,U,1310,3.860
1,U,1320,3.860
2,P,1730,3.500
2,P,1810,3.830
2,P,1820,3.830
2,P,1830,NULL
2,U,1220,0.000
2,U,1230,4.500
2,U,1310,4.570
2,U,1320,4.570
];
right join(Data)
Data1:
Load ID,Career,sum(Strm) as Strm
RESIDENT Data WHERE GPA='0.000' Group by ID,Career;