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.

[sunny_talwar]

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;

[Anonymous]

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

[Anil_Babu_Samineni]

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

[lyeynant]

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

[sunny_talwar]

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)

[ancuta_barbu]

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.

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.

[ancuta_barbu]

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: