Filter with conditions

Help Studio
1

dailynotes.xlsx (8.5 KB)

Hi, i have scenario where Bot has to get 2 most recent daily notes date prior to Progress notes dated “10/22/2023”. I mean Daily notes date should not be greater than Progress notes date And also Progress notes date should be not equal to daily notes dates

finally the output should be the two daily notes
10/18/2023 and 10/16/2023

please provide the workflow… i will be helpful to me

2

Hi @Nikhil_Katta

Follow the below steps if you want to print the dates in a Log Message or Message Box:
=> Use Read Range Workbook to read the excel and store the output in a datatable say dtNotes.

Assign activity -> dtProgressNotes= (From row In dtNotes.AsEnumerable()
                          Where row.Field(Of String)("Doc Type").StartsWith("Progress Note")
                          Select row).CopyToDataTable()
Assign activity -> dtDailyNotes= (From row In dtNotes.AsEnumerable()
                        Where row.Field(Of String)("Doc Type").StartsWith("Daily Note")
                        Select row).CopyToDataTable()
Assign activity -> dtProgressNotes.DefaultView.Sort= "DOS"
Assign activity -> dtProgressNotes= dtProgressNotes.DefaultView.ToTable()
Assign activity -> mostRecentDates= (From progressNoteRow In dtProgressNotes.AsEnumerable()
                   From dailyNoteRow In dtDailyNotes.AsEnumerable()
                   Let progressNoteDate = CDate(progressNoteRow("DOS"))
                   Let dailyNoteDate = CDate(dailyNoteRow("DOS"))
                   Where progressNoteDate > dailyNoteDate
                   Select dailyNoteDate).Distinct().OrderByDescending(Function(d) d).Take(2).ToList()
Message Box -> String.Join(vbCrLf,mostRecentDates)

Note: dtProgressNotes, dtDailyNotes is of DataType System.Data.DataTable. mostRecentDates is of DataType System.Collections.Generic.List(System.DateTime)

image
image1128×751 42.2 KB

Sequence16.xaml (10.4 KB)

Follow the below steps if you want to print the output in an excel:
=> Use Read Range Workbook to read the excel and store the output in a datatable say dtNotes.

Assign activity -> dtProgressNotes= (From row In dtNotes.AsEnumerable()
                          Where row.Field(Of String)("Doc Type").StartsWith("Progress Note")
                          Select row).CopyToDataTable()
Assign activity -> dtDailyNotes= (From row In dtNotes.AsEnumerable()
                        Where row.Field(Of String)("Doc Type").StartsWith("Daily Note")
                        Select row).CopyToDataTable()
Assign activity -> dtProgressNotes.DefaultView.Sort= "DOS"
Assign activity -> dtProgressNotes= dtProgressNotes.DefaultView.ToTable()
Assign activity -> recentDailyNotes= (From progressNoteRow In dtProgressNotes.AsEnumerable()
                    From dailyNoteRow In dtDailyNotes.AsEnumerable()
                    Let progressNoteDate = CDate(progressNoteRow("DOS"))
                    Let dailyNoteDate = CDate(dailyNoteRow("DOS"))
                    Where progressNoteDate > dailyNoteDate
                    Select dailyNoteRow).Distinct().OrderByDescending(Function(d) CDate(d("DOS"))).Take(2).CopyToDataTable()

Note: dtProgressNotes, dtDailyNotes and recentDailyNotes is of DataType System.Data.DataTable.
=> Use Write Range Workbook to write the data back to excel.

image
image791×744 6.93 KB

Sequence15.xaml (10.6 KB)

Regards