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(r) 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(r) 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!
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.
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.
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)
@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 ?
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.
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.
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
Anil_G
A_Learner
ashokkarale
lrtetala
Ritaman_Baral