Group datatable by column

Help Activities
1

Hello everyone,

I am looking for a way to optimize my current reminder automation by lowering the number of reminder mails that are being sent out. The data is stored in a datatable called “dtReminderData” and looks something like this (only demonstration purposes):

image

Currently I am using a simple “for each row” activity to send out a mail for each of the documents. Now I would like to group by the processor and send only one reminder mail containing all the DocNos which he/she is responsible for. In this example the reminder mail for Processor1 should contain both DocNo “123456” and “999000”. I had a look at other posts about similiar topics in the forum but my LINQ-skills sadly are not good enough. Is there maybe another way? Could anyone here help me out? Thank you in advance.

2

Hi
Check this discussion for more details

Cheers @Oezkurt_Bilal

3

@Oezkurt_Bilal

One simple way would be first identify all the unique processors using dt.DefaultView.ToTable(True,"Name (Processor)") and stored in uniquedt

Now use the for loop on unique dt

then use filter datatable inside the for loop on original dt and filter Name (Processor) column with currentRow(0).ToString and store in filtereddt

Now filtereddt will have all the data of only one processor…you can get doc no using String.Join(",",dt.AsEnumerable.Select(function(x) x("DocNo").ToString)) which will be comma separated doc numbers you can use

Hope this helps

cheers

4

Hi,

There are some approaches to achieve this. The following sample is one of them using Dictionary.

image
image868×1142 39.6 KB

Sample20230921-2L.zip (10.1 KB)

Regards,

5

Thank you for your replies. I have assigned the following to a new data table:

(From d In dtReminder.AsEnumerable
Group d By k=d(“User (Processor)”).toString.Trim Into grp=Group
Let ra = New Object(){k, String.Join(“;”, grp.Select(Function ( r ) r(“DocNo”).toString.Trim).toArray)}
Select dtResult.Rows.Add(ra)).CopyToDataTable

Now I do get each DocNo for every processor into one row, but the headers are not correct anymore. The user name is listed in the DocNo column. I would also like to have the other columns filled out. Is there a way to add those or do I need to join the initial data table dtReminder and the new dtResult?

The current result looks like this:
image

6

Hi,

If you need to keep original format, the following will help you.

image
image828×628 32.8 KB

(From d In dtReminder.AsEnumerable
Group d By k=d(“User (Processor)”).toString.Trim Into grp=Group
Let ra = New Object(){String.Join(“;”, grp.Select(Function (r) r(“DocNo”).toString))}.Concat(grp.First().ItemArray.Skip(1)).ToArray
Select dtResult.Rows.Add(ra)).CopyToDataTable

Sample20230922-2L.zip (9.3 KB)

Regards,

7

Thank you very much.

1 Like
8

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