Hello guys, Could anyone help me how I can achieve this using UiPath based on condition Name & ID is same?
Example :
Name | ID | Age | Last
James 1 Bond
Nick 2 25
James 1 30
Output :
Name | ID | Age | Last
James 1 30 Bond
Nick 2 25
outputDataTable = (From row In inputDataTable.AsEnumerable()
Group row By Name = row.Field(Of String)("Name"), ID = row.Field(Of String)("ID")
Into Group
Select Group.OrderByDescending(Function(r) If(IsDBNull(r("Age")), 0, r.Field(Of Int32)("Age"))).First()
).CopyToDataTable()
Note:Please change the column datatypes as per your Excel.Like sometimes ID and Age may be Double or String or int32
@rlgandu Thank you for your response, can’t we make it dynamic, i mean to say, i have many columns(50+), instead just “Age” , can we do dynamically, and merge based on Name & ID match ?
All the duplicate items will replaces & missing items will be added ?
outputDataTable = (From row In inputDataTable.AsEnumerable()
Group row By Name = row.Field(Of String)("Name"), ID = row.Field(Of String)("ID")
Into Group
Let mergedRow = inputDataTable.NewRow()
Let mergedColumns = Group.SelectMany(Function(r) r.Table.Columns.Cast(Of DataColumn)().Where(Function(c) Not mergedRow.Table.Columns.Contains(c.ColumnName)).Select(Function(c) New With {Key .Column = c, Key .Value = r(c)}))
Select mergedRow.ItemArray = inputDataTable.Columns.Cast(Of DataColumn)().Select(Function(c) If(Group.Any(Function(r) Not IsDBNull(r(c)) AndAlso Not String.IsNullOrEmpty(r.Field(Of String)(c.ColumnName))), Group.OrderByDescending(Function(r) r.Field(Of String)(c.ColumnName)).First()(c), DBNull.Value)).ToArray(), mergedRow
).CopyToDataTable()
Please try this hope this works for you
groupedData = From row In dataTable.AsEnumerable()
Group row By key = New With {Key .Name = row.Field(Of String)("Name"), Key .ID = row.Field(Of Integer)("ID")}
Into Group
Select New With {
.Name = key.Name,
.ID = key.ID,
.Age = Group.Max(Function(r) If(IsDBNull(r("Age")), 0, r.Field(Of Integer)("Age"))),
.Last = Group.FirstOrDefault(Function(r) Not IsDBNull(r("Last")) AndAlso Not String.IsNullOrEmpty(r.Field(Of String)("Last"))).Field(Of String)("Last")
}
Cheers
Hi @Jena,
Please try the below Linq code once if this works.
(From data In ((From row In dtInput.AsEnumerable() Group row By Name=row.Field(Of String)(“Name”), ID=row.Field(Of String)(“ID”) Into grp=Group Where grp.Count>0 Select grp.Select(Function(dRow) dRow).CopyToDataTable).ToList)
Let arrResultObj = (From columnName In data.Columns.Cast(Of DataColumn) Let result_FirstNonEmptyValue= data.AsEnumerable.firstOrDefault(Function(row1) Not String.IsNullOrWhiteSpace( row1(columnName).tostring) ) Select If( result_FirstNonEmptyValue Is Nothing, “”,result_FirstNonEmptyValue(columnName )) ).ToArray
Select dtOutput.Rows.Add(arrResultObj(0),arrResultObj(1),arrResultObj(2),arrResultObj(3))
).CopyToDataTable
Note: Here on this code require to add the individual column values (like arrResultObj(0) etc) so that it will be append into the output. if you have more columns than these 4 please add those index of column accordingly. I will see into it if we have some ways to avoid this stuff.
I am seeking help on this if someone knows about how to add rows into datatable without using the index from array of Object. (eg: array of Object convert to datarow, instead of adding individually)
please find the sequence screenshot FYI.
Thanks
Sorry for late reply, i see this error
Thank you for your response, can’t we make it dynamic, i mean to say, i have many columns(50+), instead just “Age” , can we do dynamically, and merge based on Name & ID match ?
All the duplicate items will replaces & missing items will be added ?
Hi @Jena ,
Can you try once below the code for your a few of the columns and let me know if this working.
(From data In ((From row In dtInput.AsEnumerable() Group row By Name=row.Field(Of String)(“Name”), ID=row.Field(Of String)(“ID”) Into grp=Group Where grp.Count>0 Select grp.Select(Function(dRow) dRow).CopyToDataTable).ToList)
Let arrResultObj = (From columnName In data.Columns.Cast(Of DataColumn) Let result_FirstNonEmptyValue= data.AsEnumerable.firstOrDefault(Function(row1) Not String.IsNullOrWhiteSpace( row1(columnName).tostring) ) Select If( result_FirstNonEmptyValue Is Nothing, “”,result_FirstNonEmptyValue(columnName )) ).ToArray
Select dtOutput.Rows.Add(arrResultObj(0),arrResultObj(1),arrResultObj(2),arrResultObj(3))
).CopyToDataTable
Thanks
I was having other issues, after resolving, i see this last issue above screnshot
FinalDt = (From row In inputDataTable.AsEnumerable()
Group row By NAME = row.Field(Of String)("NAME")
Into Group
Let mergedRow = inputDataTable.NewRow()
Let mergedColumns = Group.SelectMany(Function(r) r.Table.Columns.Cast(Of System.Data.DataColumn)().Where(Function(c) Not mergedRow.Table.Columns.Contains(c.ColumnName)).Select(Function(c) New With {Key .Column = c, Key .Value = r(c)}))
Select mergedRow.ItemArray Is inputDataTable.Columns.Cast(Of System.Data.DataColumn)().Select(Function(c) If(Group.Any(Function(r) Not IsDBNull(r(c)) AndAlso Not String.IsNullOrEmpty(r.Field(Of String)(c.ColumnName))), Group.OrderByDescending(Function(r) r.Field(Of String)(c.ColumnName)).First()(c), DBNull.Value)).ToArray(), mergedRow
).CopyToDataTable()
Thanks for reply , @Sagar1
I see this error , as i have many columns (50+) , it need to be dynamically adjust.
Assign: Input array is longer than the number of columns in this table.
Hello @Jena ,
Thank you for the update. Please check the below code, there has a small update which I thought will not work initially but it is working in my case
(From data In (From row In dtInput.AsEnumerable()
Group row By Name=row.Field(Of String)("Name"), ID=row.Field(Of String)("ID") Into grp=Group
Where grp.Count>0
Select grp.Select(Function(dRow) dRow).CopyToDataTable
).ToList
Let arrResultObj = (From columnName In data.Columns.Cast(Of DataColumn)
Let result_FirstNonEmptyValue= data.AsEnumerable.firstOrDefault(Function(row1) Not String.IsNullOrWhiteSpace( row1(columnName).tostring) )
Select If( result_FirstNonEmptyValue Is Nothing, "",result_FirstNonEmptyValue(columnName ))
).ToArray
Select dtOutput.Rows.Add( arrResultObj)
).CopyToDataTable
I am getting the output like the below on right hand side sheet. Please cross verify from your end and update once. Here on this code now it will be dynamically adjust the columns.
Attached the xaml.
MergeTwoRows.xaml (6.9 KB)
Note: after applying this code if you are getting any issue please provide the error screenshot as well.
Thanks
Dim dtResult As DataTable = dtInput.Clone()
' Group by Name and ID
Dim groupedData = dtInput.AsEnumerable() _
.GroupBy(Function(row) New With { Key .Name = row("Name").ToString(), Key .ID = row("ID").ToString() })
' Merge rows
For Each Group In groupedData
Dim mergedRow = dtResult.NewRow()
For Each column As DataColumn In dtInput.Columns
Dim nonEmptyValues = Group _
.Where(Function(row) Not IsDBNull(row(column.ColumnName)) AndAlso row(column.ColumnName).ToString() <> "") _
.Select(Function(row) row(column.ColumnName))
If nonEmptyValues.Any() Then
mergedRow(column.ColumnName) = nonEmptyValues.First()
End If
Next
dtResult.Rows.Add(mergedRow)
Next
' Output DataTable
dtOutput = dtResult ' Assuming dtOutput is the output DataTable to be written back to Excel
Input:
Output:
The process is as per your requirement only,It is dynamic Based on Name and ID please check this code once if any problem is there then update here.
@rlgandu Thank you, yes it seems alright, the script running now. I will check/verify all data because i have many columns.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.