multiple conditions filter collection

[budziakkamil]

hi guys,
I've an issue related to 'filter collection' action.
I've a collection with column 'column' and string like 'some value equal to XX'
Where: XX is a number between 1-1000.
I need to filter all rows with XX < 50.
Is it possible to do it using one action 'filter collection' ?
Thanks in advance

 

[sivagelli]

hello,

yes, it is possible.

Lets say i have a collection named "Coll" with fields "Field1" (text) and "Field2" (text). I want to apply filter on Field2 which has values ranging from 1-1000.

Use below for the Filter Collection action
Input:
Collection In - [Coll]
Filter - "Field2< '50'" (i assumed, column as the field in the Collection)
Ouput:
Collection Out: CollOut

On execution, CollOut will have columns Field1 and Field2 but all values in Field2 will be less than 50.

Hope this helps!

 

[budziakkamil]

hello,

yes, it is possible.

Lets say i have a collection named "Coll" with fields "Field1" (text) and "Field2" (text). I want to apply filter on Field2 which has values ranging from 1-1000.

Use below for the Filter Collection action
Input:
Collection In - [Coll]
Filter - "Field2< '50'" (i assumed, column as the field in the Collection)
Ouput:
Collection Out: CollOut

On execution, CollOut will have columns Field1 and Field2 but all values in Field2 will be less than 50.

Hope this helps!

Thank you for your reply, but it's not exactly what I need.
Collection looks like capture pic and I need to filter last 1-4 digits in string.
Thanks in advance

 

[VJR]

Thank you for your reply, but it's not exactly what I need.
Collection looks like capture pic and I need to filter last 1-4 digits in string.
Thanks in advance

Hi budziakkamil,

For this you will need to loop through the collection and in the loop use a Calc stage to retrieve the last two characters by using the Right function and at the same time add this value into a new column in the same collection.
Assuming you added them into a new column COL 2, it will look like the below

COL1 | COL2
tekst is equal to 11 | 11
tekst is equal to 8 | 8

Now if you apply the filter on COL2 as < 50 then it will filter the rows correctly.
Make sure to have the data type of the new column as Number.
Also Trim the value before inserting it into the new column so that it will remove the <space> when you capture a single digit number like the below as you are getting the last two characters ie; space and 8.
"tekst is equal to<space>8". After your work is done its up to you to delete the new helper column or not.

If you have a very large volume of data and if you want to avoid loop then you will need to copy the data to Excel and then use the Excel's Right Formula and do the same before getting the data back to the collection.

 

[venkat ramana]

HI

hi guys,
I've an issue related to 'filter collection' action.
I've a collection with column 'column' and string like 'some value equal to XX'
Where: XX is a number between 1-1000.
I need to filter all rows with XX < 50.
Is it possible to do it using one action 'filter collection' ?
Thanks in advance

Hi budziakkamil,
As I can see the column has value like below.
##############
tekst is equal to 11
tekst is equal to 14
tekst is equal to 18
tekst is equal to 21
tekst is equal to 12
tekst is equal to 8
tekst is equal to 1
##############

Take another collection and loop though each value, while looping replace the TEXT that you have with null value and get the result

at the end you will see
##############
11
14
18
21
12
8
1
##############

I hope this will help you out ....

 

[sivagelli]

For this you will need to loop through the collection and in the loop use a Calc stage to retrieve the last two characters by using the Right function and at the same time add this value into a new column in the same collection.

It is mentioned in the original post that the value ranges from 1-1000. So, Right() will not serve the purpose.

Example:
If the data is something like below; Right() does not serve

tekst is equal to 111
tekst is equal to 1
tekst is equal to 10
tekst is equal to 999
tekst is equal to 1000

Is it possible to do it using one action 'filter collection' ?

I do not think we can not achieve with one action as we need to manipulate.

 

[VJR]

Hi @budziakkamil, that's a good observation made by @sivagelli about your data being more than two digits.
That makes it even simpler. You can use the InstrRev function of the Utility Strings VBO which will give the position of the first space from the end and then get the characters/numbers till that space using the string functions.
Do not restrict your mindset to a single action if adding one more Multi Calc stage would achieve your purpose.

 

[venkat ramana]

It is mentioned in the original post that the value ranges from 1-1000. So, Right() will not serve the purpose.

Example:
If the data is something like below; Right() does not serve

tekst is equal to 111
tekst is equal to 1
tekst is equal to 10
tekst is equal to 999
tekst is equal to 1000



I do not think we can not achieve with one action as we need to manipulate.

Hi
In the same loop you need to check the condition either it is < 50 or not then only allow the collection to append

 

[Anjaneevara prasad]

It is mentioned in the original post that the value ranges from 1-1000. So, Right() will not serve the purpose.

Example:
If the data is something like below; Right() does not serve

tekst is equal to 111
tekst is equal to 1
tekst is equal to 10
tekst is equal to 999
tekst is equal to 1000



I do not think we can not achieve with one action as we need to manipulate.

Hi,
In all the rows 'tekst is equal to ' is same whose length is 18
in calc stage we can use the below expression to extract the number and store it in coll1.Field2