Aggr() ?

[NewToQlik]

Hi all, below is my sample table:

ID	STRM	GPA
1	1510	0
1	1520	1
1	1530	2
2	1610	0
2	1620	3
2	1630	4

Updated table:

ID      Strm    Career      GPA
1      1510        UG          0
1      1520        UG          1
1      1530        UG          2

1      1610        PD          3

1      1620        PD          4

1      1630        PD          3.5

2      1610        UG          0

2      1620        UG          3

2      1630        UG          4

2      1710        PD          1

2      1720        PD          5
2      1730        PD          4

EDIT:

In my filter panel, I would like to a GPA filter for the maximum STRM, grouped by the ID. In this case, my desired output in the filter panel would have the GPA values '2' and '4' '3.5' and '4' . But there will be another filter for Career, so when the users select 'UG', the GPA filter pane should reflect the max(Strm) GPA for 'UG', in this case '2' and '4'.

How do I achieve this?

Thank you so much.

[martinpohl]

a secret of good performance is to create datas in data model to reduce object calculation on sheets.

so load

YourData;

left join load

ID,

max(STRM) as MaxSTRM

resident YourData

Group by ID;

left join load

ID,

STRM as MaxSTRM

ID as MaxGPA

resident YourData;

drop field MaxSTRM from YourData;

So you will get a field MaxGPA within 2 for ID1 and 4 for ID2.

Regards

[sunny_talwar]

Are you planning to make selection in this new filter that you create or is this just for display purposes?

[NewToQlik]

I would like to make a selection in this new filter.

[NewToQlik · Accepted solution]

Nevermind, my requirement has changed, I would now display the max(strm) GPA by ID and Career.

I did this in my loading script

LATEST_STRM_GPA:

LOAD ID ,CAREER,MAX(STRM) as STRM RESIDENT MAIN_TABLE

where NOT ISNULL(GPA) group by EMPLID,ACAD_CAREER;

LEFT JOIN (LATEST_STRM_GPA)

LOAD ID,CAREER,STRM,LASTEST_GPA

RESIDENT MAIN_TABLE

where exists(STRM)and exists(ID);

[Levente_Szittya]

Hi,

Topic is old, but original question has not yet been answered.

I just though it might be useful for somebody else. I guess this is what you intended to do at the beginning: 

=aggr(DISTINCT only({<GPA=P({<Strm={$(=concat(aggr(max(Strm),ID),','))}>} GPA)>} GPA),Strm,ID)

This definition works fine in terms of values shown in the filter pane. But using them on filter pane faces challenges... (i.e: selecting value '4' only ends up with a selection for value '3.5' as well...)

Does anyone have any idea why or how the original request can be done in Sense?

Best regards,

Levente