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

If you need Max STRM over just EMPLID and not ACAD_CAREER, then you can use 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,

Max(STRM) as STRM,

1 as Flag

Resident Table

Group By EMPLID;

Highlighted
Contributor II
Contributor II

Instead of displaying 1 , i would like to display the CUM_GPA. Thank you for your help!

From Sunny's script, you can use something like this FirstSortedValue(CUM_GPA, -STRM) as Flag Instead of  1 as Flag

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Contributor II
Contributor II

T1:

Load emplid,

Max(strm) as strm

From orig_file

Group by emplid;

Store T1 into t1.qvd;

Drop table T1;

Final:

Load emplid&strm as key,

Emplid,

Strm

From t1.qvd (qvd);

Left join

Load emplid&strm as key,

Cum_gpa,

Acad_career

From orig_file;

Hope it's clear for you.

This will show you only only Max value by emplid

Highlighted

Why don't you handle this on the front end using your flag? All you would need is to use something like this

Only({<Flag = {1}>} CUM_GPA)

Highlighted
Contributor
Contributor

Try with this:

GPA:

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

1000567, UUUU, 2000, 5.000

1000567, UUUU, 3000, 6.000

2000987, XXXX, 4000, 2.500

2000987, YYYY, 5000, 3.500

];

Max_map:

Mapping Load

         STRM,

         CUM_GPA

Resident GPA

;

Max_STRM:

Load

     *,

      ApplyMap('Max_map',Max_STRM) as CUM_GPA

;   

Load

    EMPLID,

    ACAD_CAREER,

    max(STRM) as Max_STRM

Resident GPA

Group by

     EMPLID, ACAD_CAREER

;

Drop table GPA;

In the data model the table looks like in the attached picture. Table.PNG

The script accounts for different ACAD_CAREER too. If this is not important then it can be removed from 'Order by' clause but then this field will be lost when we drop the initial table.

Hope this helps.

Highlighted
Contributor
Contributor

I found a simpler solution:

Load the normal inline table and then in the sheet add a table with:

  • dimension        EMPLID
  • measure          =FirstSortedValue(CUM_GPA,-aggr(max(STRM),EMPLID,CUM_GPA))


The result is this:

result.PNG