Group data table by value and join results in caret list

I have a data table with 2 columns I want to group the data by column 1 and join the data in column 2 with ^ separating each value.

I have a simple example below of data and then what I want the output to be, but I’m struggling with how to write something to accomplish this.

image

@db44

give a try on following LINQ way:

use an assign and clone the datatable structure with
left side:dtResult
right side: YourDataTableVar.Clone

Use an assign activity:
left side: dtResult
right side:

(From d In YourDataTableVar.AsEnumerable
Group d by k=d(0).toString.Trim Into grp=Group
let ra = new Object(){k, String.Join("^", grp.Select(Function ( r ) r(1).toString.Trim).toArray)}
Select dtResult.Rows.Add(ra)).CopyToDataTable

An alternate to total LINQ would be:
Keys = YourDataTableVar.AsEnumerable.Select(Function ( r ) r(0).toString.Trim).Distinct().toArray

with a for each you can iterate over the keys and the key can be used for a filter datatable to retieve the group rows

Thanks for the reply, but are D & K the headers for each column? I used this in an assign but keeps telling me ‘end of expression expected’ I must not be understanding part of it…

@db44
Oh Let US Check for Syntax issues. I will do and come Back to you. Just give me some little time

@db44
Cool thing that the Statement form post was directly working without syntax issue.

Find starter Help here:
2Col_1Key_StringConcat_2Col-Result.xaml (9.2 KB)

Just test it at your end and let us know your feedback

The example really helped. I did not understand it was required to create the datatable prior to the assign. This works wonderfully for me. Thank you.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.