Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted

Re: How to get the highest values

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

Re: How to get the highest values

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

Highlighted

Re: How to get the highest values

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

Re: How to get the highest values

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

Re: How to get the highest values

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

Re: How to get the highest values

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

Re: How to get the highest values

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