Help creating aggregate report

[qwerty1]

Hi,

I’m having trouble creating a aggregate report.

The source data looks like this:

Grade	First Name	Surname	DOB	Subject	Class Finals	Due Resits
Sophomore	Alfie	Bradley	01/01/2011	English	True	False
Sophomore	Alfie	Bradley	01/01/2011	Music	True	False
Sophomore	Alfie	Bradley	01/01/2011	French	True	False
Sophomore	Alfie	Bradley	01/01/2011	Maths	True	False
Sophomore	Bob	Doe	02/02/201	Chem	True	False
Sophomore	Bob	Doe	02/02/2011	French	True	True
Junior	Charlie	Blogg	03/03/2013	Shop	False	True
Junior	Charlie	Blogg	03/03/2013	English	False	True

I’d like the output to look like this:

Grade	First Name	Surname	DOB	Completed Finals	Count of Entries	Resits
Sophomore	Alfie	Bradley	01/01/2011	True	4	False
Sophomore	Bob	Doe	02/02/2012	True	2	True
Junior	Charlie	Blogg	03/03/2013	False	2	True

If there’s a situation where either Class Finals or Due Resits columns has a both True and False for an individual, then the aggregate report should show True.

[ppr (Peter Preuss)]

in general we can do with grouping the data
[HowTo] Overview on different options for grouping data and processing the groups - News / Tutorials - UiPath Community Forum

[qwerty1]

Thanks @ppr - I was having trouble with the syntax. This is what I came up with to get an initial lok at the data, but having problems:

(From row In DT.AsEnumerable()
Group row By student = New With { Key .studentF = row.Item(“First Name”), Key .studentS = row.Item(“surname”), Key .studentDOB = row.Item(“DOB”), Key .studentFinal = row.Item(“Class Final”), Key .studentR = row.Item(“Due Resits”) }
Into StuGroup = Group
Select New With {
.stuFirst = student.studentF,
.stuSur = student.studentS,
.studentD = student.studentDOB,
.studentF = student.studentFinal,
.studentR = student.studentRes,
.Count = StuGroup.Count()
}).CopyToDataTable()

[Yoichi]

Hi,

Can you try the following sample?

Sample
Sample20240127-1a.zip (12.4 KB)

P.S.

I modified original data a little because it seems lack 1

Regards,

[ppr (Peter Preuss)]

Query Syntax Approach:

Prepare an empty DataTable with Build DataTable - dtResult and configure result columns
Grade First Name Surname DOB Completed Finals Count of Entries Resits

Assign Activity:
dtResult =

(From d in YourSourceDTVar.AsEnumerable
Group d by k1=d("First Name").toString.Trim, k2=d("Surname").toString.Trim, k3=d("DOB").toString.Trim into grp=Group
Let chk1 = grp.Any(Function (e1) e1("Class Finals").String.Trim.toUpper.Equals("TRUE"))
Let chk2 = grp.Any(Function (e2) e2("Due Resits").String.Trim.toUpper.Equals("TRUE"))
Let ra = new Object(){grp.First()(0),k1,k2,k3, chk1.ToString, grp.Count, chk2.ToString} 
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

---

Non Linq Approach: as described within the tutorial first part

[qwerty1]

Excellent! Thank you @Yoichi

[vrdabberu (Varunraj Dabberu)]

Hi @qwerty1

→ Read Range Workbook

Output-> dtInput
→ Use below syntax in Assign:

dtOutput= dtInput.Clone()
dtOutput= (From row In dtInput.AsEnumerable()
 Group row By
     Grade = row.Field(Of String)("Grade"),
     FirstName = row.Field(Of String)("First Name"),
     Surname = row.Field(Of String)("Surname"),
     DOB = If(row.IsNull("DOB"), DateTime.MinValue, row.Field(Of DateTime)("DOB")) ' Handle DBNull for "DOB"
 Into GroupData = Group
 Let CompletedFinals = GroupData.Any(Function(r) r.Field(Of Boolean)("Class Finals")),
     Resits = GroupData.Any(Function(r) r.Field(Of Boolean)("Due Resits")),
     CountOfEntries = GroupData.Count()
 Select dtOutput.Rows.Add(Grade, FirstName, Surname, DOB, CompletedFinals, CountOfEntries, Resits)
).CopyToDataTable()

Note: dtOutput is of DataType System.Data.DataTable
→ Write Range Workbook:
Output:

Regards