topic Re: Count of Records Related in App Development

Count of Records Related

philip_radovich — Sat, 16 Nov 2024 06:32:09 GMT

Good Afternoon,

I have a requirement on a table that I am having difficulty in fulfilling. I need a column that lists a count for the total number of users related to the same event. For instance, I have a single event record from the database that 5 people are associated with that event. I have a temp table that is calculating a count of the individuals per event. Where I am having trouble though is the customer wants to see 1 of 5, 2 of 5, 3 of 5, 4 of 5, and 5 of 5 in the table. Currently for each person associated with that event it displays the total 5, as that is the total for the event. I am trying to figure out if it is possible to do as described above? Any suggestions would greatly be appreciated.

Thanks,

Phil Radovich

Re: Count of Records Related

vishsaggi — Wed, 20 Feb 2019 21:56:58 GMT

Can you share some sample data with expected output please? Will help to help you faster.

Re: Count of Records Related

philip_radovich — Thu, 21 Feb 2019 12:52:17 GMT

I am hoping for something like the attached screenshot.

Re: Count of Records Related

vishsaggi — Thu, 21 Feb 2019 13:28:14 GMT

This is the solution which is script based. Let me know if this helps

T1:
LOAD *, AutoNumber(PersonKey, EventKey) AS Cnt Inline [
PersonKey, EventKey
1,1
2,1
3,1
4,1
5,1
6,2
7,2
8,2
9,2
10,3
11,3
12,3
];

LEFT JOIN(T1)
LOAD EventKey,
Count(PersonKey) AS RowCnt
Resident T1
Group By EventKey;

NoConcatenate
LOAD *, Cnt&' Of '& RowCnt as CntIndividuals
Resident T1;

Drop Table T1;

Re: Count of Records Related

vishsaggi — Thu, 21 Feb 2019 13:51:10 GMT

Another solution in UI chart expr try like

Using straight table add dims as
PersonKey, EventKey
3-Exprs
=Aggr(Rank(-PersonKey,4,1), EventKey, PersonKey) //Label this as Ranking
=Count(TOTAL <EventKey> EventKey) //Label this as Cnt
=Ranking & ' of '& Cnt //This will give you count of individuals

Re: Count of Records Related

philip_radovich — Thu, 21 Feb 2019 18:12:51 GMT

Hi,

Thank you for the help. I was able to get this to work but is there any way to hide and or calculate this in the front end without displaying the ranking and cnt columns? I attempted the backend update but it is duplicating the total number of events across each person in the data set.

Thank you so much!

Re: Count of Records Related

vishsaggi — Thu, 21 Feb 2019 21:12:37 GMT

Got to presentation tab of your straight table and check Ranking and Cnt fields and Check radio button that says Hide column.