Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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);
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
Are you planning to make selection in this new filter that you create or is this just for display purposes?
I would like to make a selection in this new filter.
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);
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