Filter Collection if greater than Number value

[nrenaud1]

Hi,

I am reading from an .xlsx file into a collection with thousands of rows.  I want to then filter the collection by one of the column values (Column 12) if it is > 45.  However, all of the fields in the collection are of type Text. 

Is there a way to use Filter Collection action to do this ? I cant figure out the Text format and cant compare text values to numbers.

"'" & [Data.Column 12] & "'" & " > 45"

Tryng to do something like this ?

Thanks!

[MichealCharron]

@Nick Renaud

This is a common misunderstanding of the "Filter Collection" action that you have to use the collection field notation in the filter but you have already passed the collection in through the "Collection In" parameter so you really just have to specify the column itself in the filter like the following:

"[Column 12] > 45"

Even if your "Column 12" is text type, the filter will do the conversion as long as every row has a numeric value in it.​

[nrenaud1]

@MichealCharron 

Thanks,
This expression is technically working. But the data it is filtering is incredibly inaccurate (not pulling all rows > 45, and pulling many that are < 45).

Even though every row is a numeric value in that column,​

[PvD_SE]

Hi Nick,

That sounds strange.

With a Text field containing number values, I'd expect the decision stage to be something like this:

Can you screendump some of the data in Column 12 (some values that work, and some that don't) and the data type of the field?

Happy coding!
---------------
Paul
Sweden

[MichealCharron]

@Nick Renaud

If you could provide us with a screenshot of data, as Paul JHM van Doorn (sorry, can't seem to get the @mention to work for your name) wrote in his reply, I am curious why it is not working. Right now, the only thing I can surmise is that you have zero or space padding in some of your values.

There is a function for explicitly conversion converting the values to numbers (which we use if there are decimals in the values). Using that function would be the following:

"CONVERT([Column 12], System.Decimal) > 45"​

​​​​​​

[nrenaud1]

@MichealCharron @Paul JHM van Doorn 

Added screenshots. 

Upon further inspection I see that it is sorting the data by the first number, going from 4-9 and increasing by second or third number values.

​

 

​

[ÖzençGezgin]

Hi @Nick R

My Test collection is Coll1 have 2 columns with Text type. You can create Coll2 have 2 columns with 1 column data type is text, other one is Number.


Add a calculation Stage.
Calc stage have no formula. Expression section is [Coll1] and Store Result in Coll2

Than you can use Coll2 with a number type data for filter.




Output is Coll3


Have a nice day...​

[PvD_SE]

Hi @MichealCharron,

Your first proposal assumed the column to be a Number where it was Text​ according to Nick. Hence my proposal using the ToNumber() function. In my example, I picked the column from the list as presented in the properties of the Calculation stage, hence the use of Collection.Field notation. 

Nick's screenshots clearly show that the data has type Text, not Number. While his original code may work with the two digit values, the three digits clearly will not. My example should work, either in a Decision stage while looping the collection, or in a Filter stage for that matter.

Additionally, there's an example from Özenç that converts the Text to a Number in a new column in the same collection, and then use the new column for filtering. That'll work too, but it consists of extra stages and a new column that I probably would prefer not to have.

All of the above are mho.


Happy coding!
---------------
Paul
Sweden

[MichealCharron]

Paul,

I did know it was a text column. The "Filter Collection" action uses ​a .Net DataTable.Select method which works with most of the same underlying rules as SQL. When evaluating a filterExpression, the Select method will cast a numeric value string to a number, if possible. Using the Convert function (in previous post) goes a little further in handling strings with commas or decimal points or even leading spaces.

Simple Cast Collection In

Simple Cast with Collection Out

CONVERT Function Collection In

CONVERT Function with Collection Out

[andymenon]

It's too much effort to run a simple numeric filter on data sourced from Excel. I have confirmed with BP support that unless the Excel column is formatted as a number, applying filters on collections containing number data is highly unreliable. I have experienced this myself. It is not always possible to process strongly typed Excel worksheets in real world scenarios.

As a solution, I'm coming up with a Data Filter VBO that will help us overcome such issues. Instead of building a loop to check for and evaluate numeric filter conditions, this VBO will help us apply filters in a single step and return accurate results.

Here is a preview from the documentation that will be published with the VBO. As you can see, we are applying common numeric filters to an Excel sourced collection, and it returns the expected number of records. All in a single step.



Stay tuned!