Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to get the highest values

Hi all,

EMPLIDACAD_CAREERSTRMCUM_GPA
1000477UGRD13200.000
1000477UGRD13304.750
1000477UGRD14104.860
1000477UGRD14204.860
1000477UGRD14304.820
1000477UGRD15104.730
1000477UGRD15204.730
1000477UGRD15304.740
1000477UGRD16104.740
1000477UGRD16204.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.

16 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Try this

=FirstSortedValue(Sum(CUM_GPA), -STRM)


(- to get last value)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

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

jobsonkjoseph
Creator III
Creator III

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
Not applicable
Author

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

EMPLID      CUM_GPA

100477         4.780

jobsonkjoseph
Creator III
Creator III

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
Not applicable
Author

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

Anonymous
Not applicable
Author

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;


Capture.PNG