Find Row index based on name and max date

I am extracting a datable to find the row index to initiate an action on that row.

Example:
image

In this example, I need to find the rowindex for Kevin’s most recent date. The output would be 4 in the datatable.

How do I get 4?

1 Like

Hi @Robert_Carlisle

Welcome to the community :smiley:
Did you already try these or maybe do you have xaml files for these to easily address the issue.

cheers :smiley:

Happy learning :smiley:

2 Likes

@Robert_Carlisle,

I have created a workflow based on your requirement.
( In the attached data table Name and data to be considered as headers then your expected
value of row lndex should be 3)
Please reply back to me if you need additional information on this.

Thanks,
Mohanraj.SITPROCESS.zip (654.4 KB)

1 Like

Thank you @Mohansadaiyapillai.

This file is only doing a look up for a specific date. I need to know the row index for James’ max date.

I need to find the row with the most recent date AND contains the name “James”

1 Like

Any thoughts @pattyricarte?

Hi @Robert_Carlisle,

Linq is best suitable for this kinda cases, I’d done the same using linq. So use below linq query for getting most recent date for every Name. —> by using max Function on group (group of dates for perticular Name) and I’m attaching xaml file at the end of the post for better understanding.

Follow steps below:

  1. Use readRange activity to get excel data in DataTable variable.
  2. Assign List1 (Type List (Of String)) =
(From n In dta.Select() 
Group n By Name = n(“Name”).ToString Into Group 
Select CStr(Group.max(Function(x) CDate(DateTime.ParseExact(x.Item("Date").ToString,"M/dd/yyyy",System.Globalization.CultureInfo.InvariantCulture))))).ToList()

—> by this you’ll get List of recent dates for distinct Names.

  1. Once you have the List, it’s on you to put it in DataTable, as shown in below shot.

image

here’s the workflow, If you need it.
Dates.zip (19.0 KB) :slight_smile:

Thats great but how do I get the row index from the original datatable?

“Name” is a variable that I would be using to search.

Use sort datatable activity on row.item(1) so you can sort it asc or desc. order.

For each row in DT
rowIndex=DT.rows.indexof(row)
next

That doesn’t work either because the date is not unique in the list of rows. That will only display the first time that date is shown. Here is an example table I extracted and need to find the row index for.

In this example, I am looking for the row index that contains most recent date that “Send parts” is in the list.

Example stage list.xlsx (8.8 KB)

And for what do you need a row index ? What are you doing when you find it ?

The Browser has this information in a table. It also has a third column with a link to select. I am trying to get the row index to identify which link to select.

How I was hoping to accomplish this:

  • Extract Data Table
  • Use Assign Activity (VB expression to identify the row index that contains the most recent “send parts”)
  • Use Click Activity (select the appropriate link. This would be identified by the row index found in the previous step.)

I’ve been searching through everything in the forum but can’t figure out how to make this work.

Here you go.
The newest date will always be on the top.
Main.xaml (8.8 KB)

It looks like there is still an issue with this one.

That doesn’t search the original DT correctly. It is only looking for max date and not the max date with “send parts”. Here is the example with Lookup Data Table added.

The result is 8 but should be 9.
stage example.zip (9.2 KB)

my xanl filters send parts and only looks at that dates of that value.please see the attached xaml

I figured it out. I added an “if” activity for each row to find the index of the row that contains both variables. Attached is the final result. stage example.zip (9.5 KB)

Thanks for your help!

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