Filter DataTable using Startswith and count distinct values

Hi friends,

I was looking everywhere and I’m unable to find a solution to my problem. I have a datatable consisting of very large records almost 100k rows. What I wanted to do is filter Column_A by value and Column_B by Startswith and count the distinct values in Column_B.

Example:

Column A | Column_B
ABC | A10001
DEF | D10001
GHI | E20001
ABC | B10001
ABC | A10002
MNO | H10007

I will filter Column_A by “ABC” Column_B Startswith(“A1”), the resulting count for Column_B should be 2.

Visualization:
Column_A | Column_B
ABC | A10001
ABC | A10002

FilterDataTableUsingStartWith.zip (22.2 KB)

Hi,
Please find the attached work flow.

Hope it will help
please mark as solution if it works

1 Like

Thank you @Venugopal24 but I need to use command instead of UiPath activity due to the large number of records, because I am going to include it in a for each row loop and assign the resulting count value to a variable. Thank you.

Use Datatable.AsEnumerable in assign

DataRow[] rows = tbl.AsEnumerable().Where(r => r.Field<string>("Column_A").StartsWith("ABC"));

In case you face error using it refer:

Thanks @KanadMehta and @Venugopal24 ,

I think my requirements are unclear from the very beginning. I’m unable to edit the original post so please disregard it and read the following.

Hi friends,

I was looking everywhere and I’m unable to find a solution to my problem. I have a datatable consisting of very large records almost 100k rows. What I wanted to do is filter Column_A by value and Use the result in Column_B.

Example:

Column A | Column_B
ABC | A10001
DEF | D10001
GHI | E20001
ABC | B10001
ABC | A10002
MNO | H10007

I will filter Column_A by “ABC”

Visualization:
Column_A | Column_B
ABC | A10001
ABC | A10002
ABC | B10001

Next is I must know the following:

  1. Is all items that starts with “A1” in Column_B are just the same. e.g. all are A10001 in Column_B.
  2. If all items in Column_B starts with “A1” I must know how many distinct values starting with “A1” is in Column_B. E.g. A10001 and A10002 = 2 counts
  3. If there are other values in Column_B other than that starts with “A1”.

Thank you everyone. I hope I made it clear.

i have have Absolute Values, you can of course use the variables with that.

iamthejuan.zip (22.5 KB)

Happy Automation

Thank you @Robott,

Thank you so much for the effort, but there are truly many records in the datatable and I’m hoping no to resort to UiPath activities like filtering because it is slow. Thank you.

At least give a try :slight_smile:
Filtering is generally very fast. Reading such a big record maybe slower.

@iamthejuan
Find some starter help here:
iamthejuan_FilterA_ColBStatistics.xaml (8.6 KB)

1 Like

Thanks nice explanation…

Thank you @ppr, @Robott, @KanadMehta @Venugopal24 .

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