[LINQ]Use linq to join all rows that contain two equal column values with separators ;

[Felipe_Domingos]

Hi, coul someome help me to join all rows into a datatable where 2 colunmns values are equals?

I have the follow datatable:

And I want to join all columns with the same Code and Postpone, merging only the columns Reference,Code and Value . But I also want to keep the rows that are not the same.

The expected result is something like this:

[Israel_Silva]

you can use Join datatable to do this,

Could you send the tables(excel file)?

Regards
Israel

[Gokul001 (Gokul Balaji)]

Hi @Felipe_Domingos

Kindly share the sample Input excel file

Regards
Gokul

[Gokul001 (Gokul Balaji)]

Hi @Felipe_Domingos

Welcome to UiPath community

How about this expression?

(From d In DtBuild.AsEnumerable
Group d By k=d(2).toString.Trim, k2=d(4).ToString.Trim Into grp = Group
Let nj = String.Join(" ; ",grp.Select(Function (n) n(0).toString.Trim))
Let Value = String.Join(" ; ",grp.Select(Function (n) n(5).toString.Trim))
Let code = String.Join(" ; ",grp.Select(Function (n) n(6).toString.Trim))
Let ra = New Object(){nj,grp.First()(1),k,grp.First()(3),k2,Value,code}
Select r = DtOutput.Rows.Add(ra)).CopyToDataTable

Check out the XAML file

FilterRowvale.xaml (7.8 KB)

Regards
Gokul

[Felipe_Domingos]

I already try this, linq is the only solution

[Felipe_Domingos]

Hi, follow
CobrancaExemplo.xlsx (12.6 KB)

[Felipe_Domingos]

Thanks, i will try it now

[Gokul001 (Gokul Balaji)]

Have you try with my solution @Felipe_Domingos

[Gokul001 (Gokul Balaji)]

Output @Felipe_Domingos

Workflow

FilterRowvale.xaml (7.0 KB)

Regards
Gokul

[Felipe_Domingos]

@Gokul001 Thanks a lot man, the only thing missing is that I needed the validation to be done for lines that have the same “Reference AND Postpone”, but LINQ is joining for cases where lines that have the same “Reference OR Postpone”.
Can you tell me how to change this?

[Gokul001 (Gokul Balaji)]

Based on the below output provided by you @Felipe_Domingos Share some expected output

@Felipe_Domingos

The expected result is something like this:

[Felipe_Domingos]

yes, but I only need to join cases where the Reference and the postpone are the same
Where this:

Become this:

The same customer(column “Cliente”) can have more than one invoice, but those with the same Postpone will be sent in the same message. While those from different dates will be sent separately on their respective dates using Postpone on queue

[Felipe_Domingos]

@Gokul001 Can I do something like this?

[supermanPunch (Arpan)]

@Felipe_Domingos

Become this:

Hi @Felipe_Domingos ,

Could you confirm whether the Input and Output provided is proper ? It doesn’t seem to match the conditions and also the Code Column in the Output contains 7, 8 where we cannot map it back to the Input.

Let us know the correct Output if at all the one provided was incorrect which would help us then focus on the Logic that could be used in creating the solution.

[Felipe_Domingos]

Hi @supermanPunch,
Sorry for the mistake, the wrong worksheet is the input. The column “Code” is a unique key and should have followed the order of the numbers above.
The last two lines, which have values “1” and “1” are “6” and “7” respectively

[supermanPunch (Arpan)]

@Felipe_Domingos ,

To be clearer, Could you provide us with the Input excel file, So that we could work on it and provide you with the Suggestion or an Approach to Solve the issue.

[Felipe_Domingos]

CobrancaExemplo (1).xlsx (12.8 KB)
He follows

[supermanPunch (Arpan)]

@Felipe_Domingos ,

Check the below Workflow :
DT_GroupBy_Concatenate.zip (11.9 KB)

Let us know if it doesn’t work as expected

[Felipe_Domingos]

Thank you very much, this helps me a lot