How to compare the data table with "Date" column and pick first 3 unique date value rows

Help Activities
, ,
1

Hi,
could you please help me on below scenario.

  1. data table having multiple columns, from this we need to get 3 rows with distinct "date"values from input data.

Ex:Input data

Category Brand Model date
abc1 exe0001 rgg345 9/6/2024
abc2 exe0002 rgg346 9/6/2024
abc3 exe0003 rgg347 9/7/2024
abc4 exe0004 rgg348 9/8/2024
abc5 exe0005 rgg349 9/7/2024
abc6 exe0006 rgg350 9/10/2024
abc7 exe0007 rgg351 9/11/2024
abc8 exe0008 rgg352 9/10/2024
abc9 exe0009 rgg353 9/10/2024

Output:(we can pick any 3 random rows with date as unique )

Category Brand Model date
abc1 exe0001 rgg345 9/6/2024
abc3 exe0003 rgg347 9/7/2024
abc6 exe0006 rgg350 9/10/2024

TIA

2

HI @Manaswini_UI

Take assign activity and use below expression to get distinct value from your datatable
dt.AsEnumerable() _
.GroupBy(Function(row) row.Field(Of DateTime)(“date”)) _
.Select(Function(group) group.First()) _
.Take(3) _
.CopyToDataTable()

Please find attached workflow
distinctrows.zip (48.0 KB)

The image shows an Excel spreadsheet with data on the "Output" tab, listing categories, brands, models, and dates in columns A through D. (Captioned by AI)
The image shows an Excel spreadsheet with data on the "Output" tab, listing categories, brands, models, and dates in columns A through D. (Captioned by AI)1920×717 24.6 KB

I hope this will help you to get desired output

Thanks
Akash Javalekar

3

Hi @Manaswini_UI,
you can use below Linq expression to get unique and first 3 values,
Take assign activity and enter below expression in value field
builddt.AsEnumerable().GroupBy(Function(x) DateTime.Parse(x(“date”).ToString())).Select(Function(g) g.First()).Take(3).CopyToDataTable()

in To section enter filterdattable variable

refer below screenshot

The image shows a UiPath workflow containing an "Assign" activity that groups a DataTable by date, selects the first entry from each group, and copies the first three groups back into a new DataTable before proceeding to a "Message Box" activity. (Captioned by AI)
The image shows a UiPath workflow containing an "Assign" activity that groups a DataTable by date, selects the first entry from each group, and copies the first three groups back into a new DataTable before proceeding to a "Message Box" activity. (Captioned by AI)1008×212 10.5 KB

4

Hi, Thanks for your reply. Below conditions is not satisfying the with your solution.

Input: Date columns having only 2 distinct dates

Category Brand Model Modification date
abc1 exe0001 rgg345 9/6/2024
abc2 exe0002 rgg346 9/6/2024
abc3 exe0003 rgg347 9/6/2024
abc4 exe0004 rgg348 9/7/2024
abc5 exe0005 rgg349 9/7/2024
abc6 exe0006 rgg350 9/7/2024
abc7 exe0007 rgg351 9/7/2024
abc8 exe0008 rgg352 9/7/2024
abc9 exe0009 rgg353 9/7/2024

output: Need to pick 3 columns in any order

Category Brand Model Modification date
abc1 exe0001 rgg345 9/6/2024
abc2 exe0002 rgg346 9/6/2024
abc4 exe0004 rgg348 9/7/2024
5

Hi
try below linq query and let me know
dt.AsEnumerable() _
.GroupBy(Function(row) row.Field(Of DateTime)(“Modification date”).ToString(“MM/dd/yyyy”)) _
.SelectMany(Function(g) g.Take(3).Distinct()) _
.Take(3) _
.CopyToDataTable()

The image depicts a UiPath Studio interface displaying a workflow sequence with activities for reading from and writing to an Excel workbook, along with the Immediate panel showing sample data and the Output panel indicating the start of an execution. (Captioned by AI)
The image depicts a UiPath Studio interface displaying a workflow sequence with activities for reading from and writing to an Excel workbook, along with the Immediate panel showing sample data and the Output panel indicating the start of an execution. (Captioned by AI)1920×881 83.8 KB

6

9/7/2024 date is not covered in output datatable. Could you please check once

7

Hi @Manaswini_UI
try this query and let me know if its working
dt.AsEnumerable() _
.GroupBy(Function(row) row.Field(Of DateTime)(“Modification date”)) _
.Where(Function(g) g.Key <> DateTime.MinValue) _
.SelectMany(Function(g) g.Take(2)) _
.Take(3) _
.CopyToDataTable()

This image shows the UIPath Studio interface with a workflow being executed that involves reading from and writing to an Excel workbook. (Captioned by AI)
This image shows the UIPath Studio interface with a workflow being executed that involves reading from and writing to an Excel workbook. (Captioned by AI)1920×1027 103 KB

Please mark as solution if its working

Thanks
Akash Javalekar

8

Below condition is not satisfying
Input: Same date in all coulmns

Category Brand Model Modification date
abc1 exe0001 rgg345 9/6/2024
abc2 exe0002 rgg346 9/6/2024
abc3 exe0003 rgg347 9/6/2024
abc4 exe0004 rgg348 9/6/2024
abc5 exe0005 rgg349 9/6/2024
abc6 exe0006 rgg350 9/6/2024
abc7 exe0007 rgg351 9/6/2024
abc8 exe0008 rgg352 9/6/2024
abc9 exe0009 rgg353 9/6/2024
Output:
Category Brand Model Modification date
abc1 exe0001 rgg345 9/6/2024
abc2 exe0002 rgg346 9/6/2024
abc3 exe0003 rgg347 9/6/2024
9

Below condition is not satisfying
Input: Same date in all coulmns

Category Brand Model Modification date
abc1 exe0001 rgg345 9/6/2024
abc2 exe0002 rgg346 9/6/2024
abc3 exe0003 rgg347 9/6/2024
abc4 exe0004 rgg348 9/6/2024
abc5 exe0005 rgg349 9/6/2024
abc6 exe0006 rgg350 9/6/2024
abc7 exe0007 rgg351 9/6/2024
abc8 exe0008 rgg352 9/6/2024
abc9 exe0009 rgg353 9/6/2024
Output:
Category Brand Model Modification date
abc1 exe0001 rgg345 9/6/2024
abc2 exe0002 rgg346 9/6/2024
abc3 exe0003 rgg347 9/6/2024
10

Thankyou, Its working

11

Great Happy to help you

Happy Automation

12

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