Filter datatable using the known start and end values

Help Studio
, ,
1

Hi Team.

As mentioned in the below screenshot, I am having the table and the primary numbers in the tabular format, EXACTLY as shown in the figure.

image
image512×599 8.74 KB

Suppose I have the values of the start and end range as following:

  • strStart = April 2021
  • strEnd = March 2021

I need to have a filtered datatable where I can have the values as below:

Primary Number
999
888
766
543

Please let me know if we have any solution to this?
Kindly help experts: @prasath17 @Palaniyappan

Thanks and Regards,
@hacky

2

@ Experts ’ @jeevith , @postwick

3

Hi,

dt.AsEnumerable.Where(Function(x) IsNumeric( x(“Test”).ToString.Trim)).CopyToDataTable

Can you try this LINQ query for filtering only numeric values?

I hope this will work for you

5

He doesn’t only want numeric values, he wants the numeric values after the specified month.

6

Assuming exactly the data you have shown here, you’d need to read the sheet into a datatable. Then loop through the datatable and with proper logic look for strStart. Then after you’ve gotten strStart, you continue looping and stick the values into an array until you find strEnd.

Another option would be to read it into a datatable then loop through looking for strStart and strEnd. When you find each one, you put its row index into a variable ie startIndex (INT32) and endIndex (INT32). Then you can pass startIndex+1 and endIndex-1 to the Read Range activity to read back in just the values you want as rows in a secondary datatable.

However, I should point out that this is one of those times I look at the source data and ask the question…can we get it in a better format? For example, if you could get it as two columns it would be better - column 1 is Month and Year, column 2 is value.

1 Like
7
  1. Read the excel file
  2. Build a datatable to store the values
  3. Assign false to a boolean variable
  4. Iterate through the datatable with the excel-output
    4a. if row(“Primary Number”).toString.Equals(“March 2021”) → Then ‘Break activity’ → else no activity
    4b. if row(“Primary Number”).toString.Equals(“April 2021”) → Then set the boolean = true → else no activity
    4c. if YourVariableName.Equals(True) AND NOT row(“Primary Number”).toString.Equals(“April 2021”) → then add the row to the values-dt → else no activity
8

Assign Acitvity:
startIndex (Int32) =

dtData.AsEnumerable.toList.FindIndex(Function (x) x("Primary Number").toString.trim.Equals("March 2021"))

Assign Acitvity:
endIndex(Int32) =

dtData.AsEnumerable.toList.FindIndex(Function (x) x("Primary Number").toString.trim.Equals("April 2021"))

Assign Activity:
dtResult | DataType: DataTable =
dtData.AsEnumerable.Skip(startIndex + 1).Take(endindex - startindex - 1).CopyToDataTable

4 Likes