Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for
Did you mean:
Creator

## FirstSortedValue?

Hi all, below is my sample table:

ID                             Career                                           Strm                             GPA

 1 M 1530 0 1 M 1610 5 1 M 1620 4.75 1 U 1220 0 1 U 1230 4 1 U 1310 3.86 1 U 1320 3.86

 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 1 U 1220 0

 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.

2 Replies
Partner - Champion III

You could do it like this:

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;

Creator

Data:

[

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;

Tags