Datatable select record grouped by id based on field data

Help Studio
, ,
1

Hi,

Could you please help me with this?

I have a data table like this.

id Name Account RegDate ExpDate

f01 Jhon 1 01/01/2022 31/01/2022
f01 Jhon 2 30/09/2021 30/09/2022
f02 Paula 1 01/01/2022 31/01/2022
f03 Matt 1 01/01/2022 31/01/2022
f04 Jane 1 15/06/2022 15/06/2021
f04 Jane 2 01/01/2022 31/01/2022

As you can see customers might have more than one account with different expiration dates. I need to extract for each customer just one record. The record that I should extract is the one with the max expiration date.

The output table is like this

id Name Account RegDate ExpDate

f01 Jhon 1 01/01/2022 31/01/2022
f02 Paula 1 01/01/2022 31/01/2022
f03 Matt 1 01/01/2022 31/01/2022
f04 Jane 2 01/01/2022 31/01/2022

I am using this expression

(From row In data_dt
Group By id = row(“id”), x = row(“Name”) Into grp = Group
Let md = grp.Max(Function (d) d(3))
Select data_dt_max.Rows.Add({id, CDate(md.ToString).ToString(“dd-MM-yyyy”)})).CopyToDataTable

With this expression, I extract the right records but without other fields.

Do you have any suggestions on how to modify it or another method, in order to get the right result?

Thanks!

2

you need additional to filter the group members of each group to get the row with max ExpDate

3

Sorry I do not get it. What do you mean for additional filter? Thaks for the answe

4

in your LINQ:
Let md = grp.Max(Function (d) d(3))
it tries to calculate the max date, but it is not filtering the group member rows for the latest date

we assume that this it targeted output, but your LINQ will not do, due

  • it is picking up only two cols
  • it is not filtering the members

Feel free to ask for more help when you do need for the next steps

5

Hi,

thanks for helping. I’ve tried some other linq expressions, taken from vb.net documentation but they don’t work on Uipath, very likely I am doing some mistake.
Generally speaking, I need to learn How to Group a table using a certain field and do a certain number of operations on the group. Like the example before, or sum a certain value. Do you know how to find material to study or course on Academy? Or may you give me an example in Uipath? I really appreciate your help!

6

currently i was searching your topic. will have a look later on it and check for a starter help XAML.

7

grafik
grafik

(From d In dtData.AsEnumerable
Group d By k=d(0).toString.Trim Into grp = Group
Let maxMember = grp.OrderBy(Function (x) DateTime.ParseExact(x("ExpDate").toString.Trim, "dd/MM/yyyy", CultureInfo.InvariantCulture)).Last()
Select r=maxMember).CopyToDataTable

we do ordering the group memebrs on ExpDate Column Ascending and taking the last one
For the correct ordering we do parse the string into a DateTime

Ensure:
grafik

Keep in mind: Excel datevalues visually presented and the values within the datatable after read range can differ. In that case we just adopt the Date conversion after analysis

Find starter help here:
GroupBy_1Col_FilterMaxDateMember.xaml (7.6 KB)

8

For learnings have a starter help here:

9

It Works! Thanks very much!

10

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