Merge rows

Help Studio
, ,
1

Screenshot 2024-01-15 150901
If a data table contains rows with similar values in the PRFNR and EMPNR columns, then I need to merge those rows.

Screenshot 2024-01-15 151009

2

Hi @sivaramana.relangi

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:

image
image1125×724 34.4 KB

dt_Input → Output of Read Range Workbook
Outpuy_dt=> Output datatable of DataType System.Data.DataTable

Output:

image
image821×749 9.39 KB

workflow:

image
image473×561 21.1 KB

Regards

3

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

[HowTo] Overview on different options for grouping data and processing the groups - News / Tutorials - UiPath Community Forum

4

Hi,

How about the following sample?

image
image777×430 17.3 KB

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