Linq Group By and Sum

Hi there! :slight_smile:

I’m facing some problems trying to generate a consolidated Datatable using Linq (GroupBy and Sum).
I have an ID column on my Datatable (not unique) and some Double values, like an Invoice ID and it items with different values. I’d like to generate a new Datatable with an unique invoice ID and sum it’s values.
Here are some methods that I’ve tried. Some of them return an error informing that UiPath uses Strict Only, hence my sintax does not explicit declare the data type for each variable, but I’m not aware of how making it works.

dtConsolidated = dtInvoice.AsEnumerable().GroupBy(Function(row) row.Item(“Invoice number”)).Select(Function(group) New With {.Grp = group.Key, .Sum = group.Sum(Function® Double.Parse(r.Item(“Item Value”).ToString()))})

dtConsolidated = From row In datinvoice.AsEnumerable()
Group row By NF = New With {
Key .NFNum = row.Item(“Invoice number”)
} Into NFGroup = Group
Select New With {
.NFNovePosicoes = NF.NFNum,
.Sum = NFGroup.Sum(Function® Double.Parse(r.Item(“Item Value”).ToString()))
}

Also, I’d like to have a Datatable as return type, not an IEnumerable.
Thank you’ll in advice!

Best regards,
Jean Andrade

Hi there,

I think I got this working. I don’t use .GroupBy much at all though.

The reason you get the Strict Only error is because you need to convert this to a list or array. Also, as far as I can tell the GroupBy doesn’t return anything that can be used with .CopyToDatatable. However, with the “Generate Data Table” activity, you could convert it, or Write it to a CSV file then Read Range.

String.Join(System.Environment.Newline,(From row In dt.AsEnumerable()
Group row By NF = New With {
Key .NFNum = row.Item(“Column1”)
} Into NFGroup = Group
Select New With {
.NFNovePosicoes = NF.NFNum,
.Sum = NFGroup.Sum(Function( r ) Double.Parse(r.Item(“Column2”).ToString()))
}).ToList)

I used what you already had and added .ToList and joined the list to a string in order to output the data. You can change dt, Column1 and Column2 back to your names.

Hope this helps resolve it.

Thanks.

1 Like

Thanks a lot, Clayton! You really helped me out here!
I’ll work on putting it back in a DataTable.

Best regards,
Jean Andrade

Hey Clayton!

I’m facing some difficulties when putting the data back to a Datatable or writing it to an Excel File.
Could you show me an example of it? I found some topics about converting Matchs result to datatable but it didn’t work for me.

Thanks again!
Best regards,
Jean Andrade

Hello,

I’m not sure this is the best way, however I got this to work by manipulating the data to a delimitted format using the below steps.

  1. Store GroupBy expression to a variable of type IEnumerable< Object >

The Output of this returns:
image

  1. Concatenate the header to the items in the list using .Split and .Select

Without the headers, we have this:
image

So our new output looks like this with headers:
image

  1. Lastly, convert to a table.
  • Using either Generate Data Table or Write Text File / Read CSV

I chose to use Write Text File because I have issues with Generate working.
So below shows an example of this.
image

Hopefully this is helpful.

Thanks, and Regards.

C

3 Likes

Good afternoon friend, I have a question and what would be the solution:
in the grouping activity:
DTleer.AsEnumerable (). GroupBy (Function (x As DataRow) x.Item (“SELLER”)) I modified it as a parameter for arguments:
DTleer.AsEnumerable (). GroupBy (Function (x As DataRow) x.Item (in_NombreHead)) when I made the modification I do not accept it. I attach my activity to find a solution thousand thanks for the collaboration Group Excel.zip (450.2 KB)

1 Like

can you help me with this ? How do I group by one column in the datatable and take the sum?

@Jean_Andrade , @ClaytonM,
hi guys a really dumb question is there somewhere other than the uipath studio that we can try these queries and see that they work . since these queries are specific to VB , are you trying this on visual basic in visual studio before typing into the studio ?

Hi @dennis,
You can use this one also.

Regards
Balamurugan.S

1 Like

You probably could try it in other coding shells like Visual Studio, but for me, I just take the data table or list that Studio is generating, then test my queries in studio to make sure it is returning the correct output.

And yeah also, there are some activities (and more to come probably) that will accomplish some of these query actions.

Regards.

This has problem when you data has comma (,) in it. :cry:

Hello Clayton.
I have build up a similar query but I am facing issues with null values, as my datatable being processed can have null values.
Could you please advise on how we can extend the Group.Sum query to check, first if the value is null and default to zero, and then apply the Group by function.
Any direction for solution is appreciated.

Thank you!

Hi.

I’m trying to answer this quick so I won’t spend much time, but if you are using .Sum(), you can use an “inline IF statement” to use a ‘0’ for non-numeric values.

For example,
group.Sum(Function(r) If(IsNumeric(r("columnname").ToString.Trim), CDbl(r("columnname").ToString.Trim), 0) )

The reason I used .ToString.Trim next to the value, is in case the number has end spaces but is still a number

Hope this helps.

Regards.

Thank you, Clayton.
I have worked up the solution using the If statement and it works.
Appreciate your help!

Cheers!