Find unique from 1 column, check 2nd column unique, send mail with both uniqe and un-unique answer

Help Studio
1

I have this data.
image

Find the rows with same CoursePayID and then find where CourseName is same and show result and then also show where course name is not same for same CoursePayID. The result should be like this… in One file

CourseName: C#
CoursePayID: 725
CoursePrice: 550
Participents: Henrik, Peter Niels

CourseName: UiPath
CoursePayID: 725
CoursePrice: 800
Participents: Anders, Tony, Adward

Total Price: 1350 (this price is a collection by CoursePayID)

Plus I also need result on other course in seprate file. I couldt figur out I should use Group by function for that login or normal if statement to do this…

Hope someone can help.

@efelantti can you help on that??

2

Hi,

Can you try the following sample?

image
image730×1149 34.2 KB

dict = dt.AsEnumerable.GroupBy(Function(r) r("CoursePayID").ToString).ToDictionary(Function(g) g.Key,Function(g) g.CopyToDataTable)

dtTemp = currentItem.Value.AsEnumerable.GroupBy(Function(r) r("CourseName").ToString).Select(Function(g) dt.Clone.LoadDataRow({g.First.Item("CourseName"),g.First.Item("CoursePrice"),g.First.Item("CoursePayID"),String.Join(",",g.Select(Function(r) r("Participents").ToString))},False)).CopyToDataTable

sumValue = currentItem.Value.AsEnumerable.Sum(Function(r) CInt(r("CoursePrice").ToString)).ToString

Sample
Sample20231214-1aL.zip (7.7 KB)

Regards,

3

I wanted to send result to manager as a mail sorted for coursepayid.
Can you explane how to do so it look like this…

CourseName: C#
CoursePayID: 725
CoursePrice: 550
Participents: Henrik, Peter Niels

CourseName: UiPath
CoursePayID: 725
CoursePrice: 800
Participents: Anders, Tony, Adward

Total price 1350 (This price is a collection by coursepayid)

4

Can someone hlep … I have’t got solution of that yet.

5

Can you try the attached solution?

Done with looping instead of GroupBy, so most likely there is a more efficient solution but this seems to get the job done.

DataTableGrouping.zip (14.9 KB)

6

Can you help with the other solution you gave … it has the right data. output–
I just need to put in email.

7

You can set the body of the email to

OutputTexts("725").ToString
8

Can you explain what this is doing???

9

Seems to group the rows by CourseName and then combining the Participents to one column for matching CourseNames.

When you run it in Debug you can check the Locals panel and check variables contents.
Eg.
currentItem.Value:
image
dtTemp:
image

10

Hi @Ellen,

Please find the attached xmal code. The code has built, still if facing issue? the video will pop out by techystack channel by comming monday at 5 PM.

Link:- https://youtu.be/LKKCo5BwEo0
Channel link:- https://www.youtube.com/channel/UCtx5wWVbY-NuCKclc6v7NOA

Regards,
Techystack
Challenge 27.zip (24.7 KB)