Group and divide Data Table using LINQ

[hannes.goldfuss]

Hello,

I have an issue with dividing a datatable in two seperate datatables. I want to group them according to one column. After this all rows with only one entry in this group should go to one datatable, the other one (<1 entry) need to go in another.

I wrote the LINQ statement in SQL but have struggle converting it to LINQ.

Here are my current LINQ statements:

(From d In dtAllData.AsEnumerable
Group d By d(3) Into g = Group
Where g.Count() = 1
Select { Key = g.Key, Count = g.Count() }).CopyToDataTable

(From d In dtAllData.AsEnumerable
Group d By d(3) Into g= Group
Where g.Count() > 1
Select { Key = g.Key, Count = g.Count() }).CopyToDataTable

I am using them in Assign Activities.

As I am a new user I cannot upload anything but I hope someone could help me anyways.

[supermanPunch (Arpan)]

@hannes.goldfuss

(From d In dtAllData.AsEnumerable
Group d By d(3) Into g = Group
Where g.Count() = 1
Select { Key = g.Key, Count = g.Count() }).CopyToDataTable

Hi @hannes.goldfuss ,

I think the corrected Code should be like below :

(From d In dtAllData.AsEnumerable
Group d By k=d(3) Into g = Group
Where g.Count() = 1
Select g.First).CopyToDataTable

(From d in dtAllData.AsEnumerable
Group d by k=d(3) into g=Group
Where g.Count > 1
Select g.ToList).SelectMany(Function (x) x).CopyToDataTable

Let us know if it doesn’t work.

[gabrielribas4 (Gabriel Ribas)]

Hey @hannes.goldfuss! Welcome to the community!!! Lets go to the solution!

I will use this test file!

Test2.xlsx (8.7 KB)

• Step 1: Let’s create a DT with the structure that will store our GROUP table. Let’s store in the DT variable “dt_result”
  

  
  
  
  

• Step 2: Let’s read the data from this worksheet and store it in a DT “dt_input”
  

  

• Step 3: Let’s do our Group By
  

  

(From p In dt_input.AsEnumerable()
Group By
col1=p("Name").ToString
Into Group
Select dt_result.Rows.Add({
	col1,Group.Count()
	})).CopyToDatatable

• Step 4: Filter the first data table with count value = 1
  
  

(From r In dt_result.Select()
Where CInt(r("Count")) = 1
Select r).CopyToDataTable

• Step 5: Filter the first data table with count value > 1
  
  

(From r In dt_result.Select()
Where CInt(r("Count")) >1
Select r).CopyToDataTable

Hope it helps!

Tha .xaml.
Main.xaml (11.8 KB)

[hannes.goldfuss]

Thank you both for helping me! It works now!

[ppr (Peter Preuss)]

@hannes.goldfuss

the last line is triggering the question which is to clear

Select { Key = g.Key, Count = g.Count() }).CopyToDataTable

it will not have a return a datarow sequence, and copytoDataTable will expect such a sequence

Case: the group members are to use
Then proceed as @supermanPunch has mentioned

Case: the target datatable should be of data column structure: Key, GroupCount
Then do following:

• define an empty target datatable e.g. with build datatable activity and configure the 2 cols - dtTarget

Assign Activity:
LHS: dtTarget
RHS:

(From d In dtAllData.AsEnumerable
Group d By k=d(3).toString.Trim Into g = Group
Where g.Count() = 1
Let ra = new Object(){k,g.Count}
Select r = dtTarget.Rows.Add(ra)).CopyToDatable

and for second set do following:
dtTarget2 = dtTarget.Clone

(From d In dtAllData.AsEnumerable
Group d By k=d(3).toString.Trim Into g = Group
Where g.Count() = 1
Let ra = new Object(){k,g.Count}
Select r = dtTarget2.Rows.Add(ra)).CopyToDatable