FirstSortedValue?

[NewToQlik]

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.

[petter]

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;

[thannila]

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;