Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

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
Highlighted

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
Highlighted
Contributor II
Contributor II

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

Highlighted
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;

Highlighted
Contributor II
Contributor II

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

EMPLID      CUM_GPA

100477         4.780

Highlighted
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.

Highlighted

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?

Highlighted
Contributor II
Contributor II

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

Highlighted
Contributor II
Contributor II

i did not get the expected outcome

Highlighted

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