How to get the first value of a filtered table?

Help
, ,
1

Hi!

So, I’ve used the Filter Table activity and I got a result of around 5 rows. I need to retrieve only value from the first row that appears, but since the cell number is variable (Sometimes it may be “L12”, sometimes “L15”, etc) I can’t use the “Read Cell” Activity. The column will always be the same, though.

Any ideas on how to approach this?

Thanks!

2

Hi,

Try using the Read Range. I’m assuming it’s filtering it on the spreadsheet, so if you use Read Range to a Data Table variable, then you can access each row directly.

For example,
datatablevariable.Rows(0).Item(“L12”).ToString

(0) tells it to use the first row - (1) would be the second row, etc
and .Item(“L12”) is the column.

Regards.

3

Hi Clayton!

I got the gist of your idea, but I’m not entirely sure on how to implement it. I’ve used the Read Range activity to read the whole sheet and then the assign activity to introduce what you wrote there. Was that your idea?

Capture

I got the following error while trying it:
Assign : Column 'L12 does not belong to table.

Thanks a lot for the help!

4

Sorry that was my fault. “L12” should have been the Column name instead.
So basically it is datatable.Rows(rowindex).Item(columnindex).ToString

If you use “Add Headers” in the properties of your Read Range you can use the column names found on the spreadsheet. However, that is only if your spreadsheet has column names. Otherwise, you need to use a numeric value that represents that column, like 0 for A, 1 for B, 2 for C, and so on.

datatable.Rows(0).Item(0).ToString
^that will return the first row and first column

datatable.Rows(0).Item(1).ToString
^that will return the first row and second column

datatable.Rows(0).Item(“Valor”).ToString
^that will return the first row and the column with header “Valor” assuming you have a Valor column header

Hopefully that made more sense.

Thanks.

C

1 Like
5

Hi All,

I was trying something similar to this, scraping some data from a web based table (unfortunately I can’t supply the web page). Anyway scraped it into a data table using Extract data activity.

I basically want to sort it DESCending and then find the top row of a specific column (submitted applications) there are lots of rows of data with various amounts I looked at several lets say values from 3 to 56 on the first page of 30 pages.

When I retrieve the first row value for submitted applications (and should be the highest) I get 9. I’m assuming that it is sorting a numerical column as strings and therefore putting 9 as the highest value.

Can anyone confirm this and if it is the case how do you sort a datatable numerically rather than alphabetically.

Any ideas welcome.

Many thanks,

image

image

image

6

For my case I suppose I could go through every row of the data table and progressively take the highest number encountered until I got to the end of the data.

However I would still like to know how to sort a data table numerically if it is possible.

7

Hi there.
If you query your data table, you can use the .OrderBy or .OrderByDescending
Either one would work as you would either take the first or the last item in the table

To use this, you need an enumerable or list, so you can place .AsEnumerable next to your data table.

This will look like this:

Assign: HighestValue = CDbl(ExtractedTable.AsEnumerable.OrderByDescending(Function(r) If(IsNumeric(r("Submitted Applications").ToString.Trim), CDbl(r("Submitted Applications").ToString.Trim), 0) ).ToArray.First("Submitted Applications"))

I kind of just came up with this really fast, so since I didn’t test it, there could be mistakes.
Essentially, you can orderbydescending the extracted data by using the value as a Double type (you can change CDbl to CInt or CLng if you want instead to remove decimals), then by using .First it takes the first Data Row in the list, and I put the column name on the end to take just that value to store to Highest Value.

You can use this in a number of ways, so it depends on how you want to use it.

but I hope this helps.

I don’t believe the Sort Data Table activity works for what you need.

Regards.

2 Likes
8

@ClaytonM Thank you very much (clearly you are an absolute legend, you knocked that up really fast)

Thanks for the explanation (really helpful)

1 Like