Consolidate Rows in Data Table

Help
, ,
1

Hi

The attached example has 2 tables. The first table contains 4 records showing the numbers of apples which John and Sam have in different boxes,

Name,#Apples,BoxNumber
John,5,1
Sam,2,2
Sam,7,1
John,1,2

I want to consolidate the rows into a new table showing that John has 6 apples and Sam has 9 apples (i.e. ignoring which box the apples are in).

Is there a solution using dt.rows.add?

consolidateTable.xaml (10.5 KB)

Advice would be greatly appreciated.

2

This should give an idea

3

Thank you @vvaidya but I also want to sum integers or decimals on the consolidated lines (rather than displaying the individual results on the same line). Also, can this not be done directly in the data table rather than using excel?

4

I’d say just do a couple of loops through the datatable using a counter either with while loops in a sequence or using decisions in a flowchart. Reference the rows like datatable(counter)(0).

First run, just get all the unique names. If it’s a new name put in an array. Check the array each loop and keep adding the uniques to the end.

The loop through the array, match the name in the datatable column 0 and then if it matches have a second array that matches the first (but of type integer or double).

At the end, just write out the arrays or else put into a new datatable or whatever you need to do with them.

5

Try this (googled and modified)

DataTable x = new DataTable(); //read range
IEnumerable<DataRow> dtrow = x.AsEnumerable();
listdt = dtrow.GroupBy(dtl => dtl.Field<string>("Name"))
                .Select(dtg => new dtg
                {
                     dtl.Field<string>("Name") = dtg.Key,
                     dtrow.Sum(d => d.Field<double>("#Apples"))                    
                }).ToList();
6

That’s neat - would that be done in a code snippet activity?

7

Hello,

I would recommend you to use datable.Compute() For this type of operation.

Here is an example that you could put in an Invoke Code Activity with two arguments (dtIn and dtOut ) Both as System.Data.Datatable

Note that i had to change your DataCollum Title from “#Apples” to “Apples” Since it conflicted for the calculation

'Create a Datatable with distinct names
dtOut = dtIn.DefaultView.ToTable(True,“Name”)

'Add Column for Total Apples
dtOut.Columns.Add(“Total Apples”

‘Fill Total Collumn For each Row
For Each row As DataRow In dtOut.Rows
row(1) = dtIn.Compute(“sum(Apples)”,"Name=’" & row(0).ToString & “'”)
Next

Let me know if you need further explanation or example without using Code activity.

Cheers

8

Thank you @vvaidya , @David_Johnston and @Florent_Salendres

An xaml example of @vvaidya 's and @Florent_Salendres solutions would be a great help.

I cannot find invoke code activity. Would these codes use the invoke com method?

9

Hi,

Find here your workflow that I modified.consolidateTableCompute.xaml (12.6 KB)

Cheers

4 Likes
10

Brilliant. Thank you very much @Florent_Salendres

11

Hi @Florent_Salendres Sorry to ask so many questions but…

How would the following line need to be changed to build in a second “Oranges” column which also needs to be summed?

dtAllRows.Compute(“sum([#Apples])”,“Name='” & row(0).ToString & “'”)

(Updated xaml attached)consolidateTableCompute_app&orang.xaml (13.2 KB)

12

Hi @Florent_Salendres

I worked it out so please ignore my previuos post. Again, thank you for your help.

consolidateTableCompute_app&orang.xaml (13.6 KB)

The apples and oranges xaml is attached for reference

1 Like
13

Hi @tobor @Florent_Salendres, @vvaidya

the above solution is when a datatable is actually built/configured with in uipath, but in case when a datatable is a result of a read from a csv/xls file, in which case the columns are by default of strings…

How to calculate for a specific column in that case which has a int/double values…

Below code is not working,

row(1) = datatable.Compute(“sum(Cdbl([Total Count]))”, “TranType='” &row(0).ToString & “'”)

with below error,

Assign : Syntax error in aggregate argument: Expecting a single column argument with possible ‘Child’ qualifier.

If I remove Cdbl conversion, row(1) = datatable.Compute(“sum([Total Count])”, “TranType='” &row(0).ToString & “'”)
The error is, Assign : Invalid usage of aggregate function Sum() and Type: String.

This is my workflow,Main.xaml (10.8 KB)

Any help ?

Thanks!

14

Is there also way how to get sum to one variable not to DT?

I have

2
3
4

not depending on Column header. Also I dont know how many rows should there be.

15

Hi @Mario. Do you mean like this?sumToVariable.xaml (7.2 KB)

1 Like
16

Working!!! :slight_smile:

17

@Dilli right, I have the exactly same issue as he did, is there any updated solution?

18

any updates ?

20

Hi @Janani_Durai,
In build data table you can create a column.
If you want there it self you can add the column values.

In add data row you can add row in the datatable.

Regards,
Arivu

22

HI @Janani_Durai,

Refer this xaml file.
DataTable.xaml (12.6 KB)

Refer this links

https://activities.uipath.com/docs/add-data-row

Regards,
Arivu