If a data table contains rows with similar values in the PRFNR and EMPNR columns, then I need to merge those rows.
You can use this code in Invoke Code activity:
' Assuming dtInput is your input DataTable
dtOutput = New DataTable()
dtOutput.Columns.Add("PRFNR", GetType(String))
dtOutput.Columns.Add("EMPNR", GetType(String))
dtOutput.Columns.Add("Zmasak", GetType(String))
dtOutput.Columns.Add("Zmasak1", GetType(String))
For Each row In dtInput.AsEnumerable()
Dim prfnr = row("PRFNR").ToString()
Dim empnr = row("EMPNR").ToString()
Dim zmasak = row("Zmasak").ToString()
Dim zmasak1 = row("Zmasak1").ToString()
Dim existingRow = dtOutput.AsEnumerable().FirstOrDefault(Function(r) r("PRFNR").ToString() = prfnr AndAlso r("EMPNR").ToString() = empnr)
If existingRow Is Nothing Then
' Add a new row if no existing row is found
dtOutput.Rows.Add(prfnr, empnr, zmasak, zmasak1)
Else
' Update the existing row with non-empty values
existingRow("Zmasak") = If(String.IsNullOrEmpty(existingRow("Zmasak").ToString()), zmasak, existingRow("Zmasak"))
existingRow("Zmasak1") = If(String.IsNullOrEmpty(existingRow("Zmasak1").ToString()), zmasak1, existingRow("Zmasak1"))
End If
Next
Invoked arguments:
dt_Input
→ Output of Read Range WorkbookOutpuy_dt
=> Output datatable of DataType System.Data.DataTable
Output:
workflow:
Regards
we could handle it within a groupby approach
- we group the data on PRFNR and EMPNR columns
- and process the groupmembers for the columns we want to merge side-by-side
Hi,
How about the following sample?
dt = dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("PRFNR").ToString,r("EMPNR").ToString)).Select(Function(g) dt.Clone.LoadDataRow({g.Key.Item1,g.Key.Item2,String.Join("",g.Select(Function(r) r("Zmasak"))),String.Join("",g.Select(Function(r) r("Zmasak1")))},false)).CopyToDataTable()
Sample20240115-3.zip (8.7 KB)
Regards,
1 Like