If same data is Repeated more than one time in a Table list, how to get its count?
Question
Application Type
Reactive

If for example,

I have a table list with various data in it. If suppose the same data gets repeated more than one time, so, how can we calculate the number of times it gets repeat. Like, XYZ is repeated 4 times, so, I want to create a separate column and show its count infront of its row. So, how to calculate that?

mvp_badge
MVP

Hi Aditya,


For that scenario, you can on a query group by the elements that are repeated and make a count on another field.

Take a look at the example below where I want to see how many users have the same name. I do a query on the entity User, group by the Name attribute, and select a Count on the Id (just right click the column and select Count):


Kind Regards,
João

Hi Joao,

When i apply this, the aggregate entities get converted to a structure. So, Cant it be done by keeping entity as an entity only?

Champion

Hi Aditya,

Yes, it will convert to structure, because there is a new field (count), that not in the original model entity.

Thanks

mvp_badge
MVP

Hi Aditya, it will create a structure.

The same thing happens in SQL when you group by, it affects your output.

What you can do however is to group by multiple fields (not just the name on my example) and they will be added to the structure.

Take into account that if you group by fields that are different, you will have multiple rows, not just one.


Kind Regards,
João

It is affecting my display output. If,I group by Items and Id count its working perfect. But, i also need to show their respective date time and ip address. If I group by them too, then the count gets disturbed means count and its ids get repeated.

mvp_badge
MVP

Hi Aditya,


If you have rows with different times it is normal that if you group by them, you will get different counts.

As I see it, you have four options:

  • Do a Max or Min on the Date time, this won't disturb the count but it will get you only the maximum or minimum date time. If that fits your use case, then this is the best scenario;
  • You are talking about a row that splits in multiple rows (the same items have different date times and IP addresses). An option, which I use often in these cases, is to include a widget like an "i" icon that when clicked opens a popup with a query to show the different dates and different IPs. This allows the user to focus on the big picture (the aggregate view with the counts) and check more detail if s/he needs to;
  • Turn your query into an advanced query and get the different IPs and different datetimes as comma-separated values using STUFF SQL command (more info here). This is a more complex and error-prone solution but it will allow you to see one a single row, all the IPs and date times;
  • Use Data Grid Reactive forge component (sample here). This will allow the user to group, filter and aggregate data as he wishes (similar to Pivot table in Excel) like on the image below:


Kind Regards,
João

Can u please show me how to implement the second option. Because according to my requirement, I cannot implement the min and max to date time.

Or Else, suggest me an other option that wont disturb the count and will show the exact date time.

Regards,

Hi Aditya,

You can use Group by on repeated column in aggregate 

Hope this helps,

Kind Regards,


Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.