Aggregating data from datatable (sumifs equivalent)

Hello everyone,

I’m looking for advice on how to create an aggregate data table to join with another data set. For instance given Table Salespeople (containing sales employees) and Table SalesActivity (containing who made the sale, amount, quantity, etc.), I want to find the total sales for each salesperson. This is nearly identical to the Excel examples for sumifs

The “brute force” solution I’ve found is:

  1. Create a copy of the SalesActivity table,
  2. Remove all but the “Employees” column,
  3. Remove duplicates on that,
  4. Add an “Amount” column back in,
  5. Finally iterate through each row in the original table, adding the amount from each row to the related Employee.

I was mildly surprised that I was able to get this to “work” after only a few tries, however this approach will take almost 15 minutes for a table containing 4k rows. Not only does this make debugging a nightmare, but it’s highly unideal to showcase UiPath’s capabilities.

Browsing the .net Framework, it seems like an alternative is to write up quite a bit of code, and use the DataColumn Expression to create a defined value (using the parent/child functionality, near bottom of page), however this seems to require creating a dataset, then adding the parent/child relationship, then finally setting the expression.

Is there a less complicated way to do this? If not, is there documentation or training on how to do this? Most of the .net examples are heavily centered in .net, which is not the easiest to replicate in UiPath (vs a fully featured C# IDE)

I’ve dived into this post (which is excellent and I hope gets expanded) on dealing with LINQ. Unfortunately I am having trouble fitting everything together. My current thoughts:

  1. I will use a for-each on the unique list of employees (Primary/Foreign key is “ID”).
  2. The LINQ will then need to check the other data table. dt_SalesActivity.asEnumerable().where(dt_SalesActivity.rows(“ID” = ID) //should downselect to rows that match that ID
  3. Somehow access the “amounts” column for those rows, and then perform a LINQ .sum operation on them, which will return a double.
  4. Assign double to the related row in the Employee table

I have some limited confidence in step 2, but no confidence in step 3. All the examples I’ve seen seem to use sum on the direct result returned by other LINQ queries. I’ll admit I’m having a bit of trouble wrapping my head around the length of some of these statements and figuring out exactly where “I am” within the relevant data structures/code.

If this was SQL, I think it’d be:
Select Sum(Amount) from SalesActivity
Where ID = Input_ID