Linq Group By and Sum

datatable
linq
studio

#1

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


#2

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.


#3

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

Best regards,
Jean Andrade


#4

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


#5

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


How do I group by one column in the datatable and take the sum?
#6

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)


#7

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


#8

@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 ?


#9

Hi @dennis,
You can use this one also.

Regards
Balamurugan.S


#10

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.