Count within many to many relationship

yatin.ajbani
yatin.ajbani
edited August 2023 in Modeling

Hi

I am trying to solve below problem and was seeking help for an optimal way.

I have a module which has a many to many relationship between Employees and Offices. I want a count of all unique employees within 1 office. For example in the below scenario, I have 3 offices with employees working from that office (1 employee can work from multiple offices). There is also a condition applied for each employee, only if that is satisfied the employee should be eligible for the count.

image.png

I am expecting to get the count as below

image.png

Thank you.

Tagged:

Best Answers

  • rob_marshall
    rob_marshall
    Answer ✓

    @yatin.ajbani

    Create a line item that concatenates both emploee and office Id. Then create another that is a counter (1) if the employee/office Id is unique…


    if isfirstoccurence(concat item, list of the module) then 1 else 0…then do a sum of that by the office id list.

  • yatin.ajbani
    yatin.ajbani
    Answer ✓

    Thank you Rob

Categories