How to get the highest values

[Anonymous]

Hi all,

EMPLID	ACAD_CAREER	STRM	CUM_GPA
1000477	UGRD	1320	0.000
1000477	UGRD	1330	4.750
1000477	UGRD	1410	4.860
1000477	UGRD	1420	4.860
1000477	UGRD	1430	4.820
1000477	UGRD	1510	4.730
1000477	UGRD	1520	4.730
1000477	UGRD	1530	4.740
1000477	UGRD	1610	4.740
1000477	UGRD	1620	4.780

From the table, i want to be able to get the value 4.780, through using the STRM.

So for example, the CUM_GPA for MAX(STRM). ( i tried MAX() , it doeskin work.)

Is there a way to do this in Qlik Sense.

Thank you for your time.

[jonathandienst]

Try this

=FirstSortedValue(Sum(CUM_GPA), -STRM)

(- to get last value)

[Anonymous]

hi, i have tried this in the script, and i got an 'Error in expression: Nested aggregation not allowed'

[jobsonkjoseph]

Hi,

you can try using preceeding load, eg:

strm1:

LOAD STRM,max(CUM_GPA) as Max_GPA Group by STRM;

strm:

LOAD * INLINE [

    EMPLID, ACAD_CAREER, STRM, CUM_GPA

    1000477, UGRD, 1320, 0.000

    1000477, UGRD, 1330, 4.750

    1000477, UGRD, 1410, 4.860

    1000477, UGRD, 1420, 4.860

    1000477, UGRD, 1430, 4.820

    1000477, UGRD, 1510, 4.730

    1000477, UGRD, 1520, 4.730

    1000477, UGRD, 1530, 4.740

    1000477, UGRD, 1610, 4.740

    1000477, UGRD, 1620, 4.780

];

Output might differ in your computer, but more or less it'll be like;

[Anonymous]

Hi, the expected output i want would be. using the highest strm,

EMPLID      CUM_GPA

100477         4.780

[jobsonkjoseph]

Hi,

Just modify above script as

strm1:

LOAD CUM_GPA,max(STRM) as STRM1 Group by CUM_GPA;

hope this is what you are looking for.

[sunny_talwar]

Do you need this in the script or front end of the app? For front end, you can try this

FirstSortedValue(CUM_GPA, -STRM)

For script, would you want to flag the max CUM_GPA with 1 or would you like to create a new field which will include the CUM_GPA based on STRM repeat on each row for a particular EMPLID?

[Anonymous]

For script, i would like to flag the max CUM_GPA with 1.

[Anonymous]

i did not get the expected outcome

[sunny_talwar]

Try something like this

Table:

LOAD * INLINE [

    EMPLID, ACAD_CAREER, STRM, CUM_GPA

    1000477, UGRD, 1320, 0.000

    1000477, UGRD, 1330, 4.750

    1000477, UGRD, 1410, 4.860

    1000477, UGRD, 1420, 4.860

    1000477, UGRD, 1430, 4.820

    1000477, UGRD, 1510, 4.730

    1000477, UGRD, 1520, 4.730

    1000477, UGRD, 1530, 4.740

    1000477, UGRD, 1610, 4.740

    1000477, UGRD, 1620, 4.780

];

Left Join (Table)

LOAD EMPLID,

ACAD_CAREER,

Max(STRM) as STRM,

1 as Flag

Resident Table

Group By EMPLID, ACAD_CAREER;