topic RE: Filtering collection in Product Forum

Filtering collection

VratHimbo — Wed, 17 Mar 2021 14:45:00 GMT

Hi team,
i've a collection with around 30 columns, but for my process i need only 5 columns, i know the name of the column, so how can i filter only these 5 columns and delete the rest?
I'm able to do that with a loop through "delete column name" collection but i think it's not the best approach, can you suggest some actions?

The second question is: how can i filter this collection and have in ouput a new collection with, for example, column1 only with numeric value, and delete the string/blank values?

Thanks in advance,
VH

------------------------------
Vrat Himbo
------------------------------

RE: Filtering collection

jhogelp — Wed, 17 Mar 2021 16:28:00 GMT

Hi Vrat Himbo,

For the first point, instead of removing the remaining 25 columns, I would create a new collections by extracting the 5 columns that I need.

For the second point in the action filter collection the inputs Filter unless the value of the field you are filtering has a pattern or starts or ends with a certain letter or word it could be filtered but if I can't give you an idea I would add it to a loop where I validate the value of the field with the Functions IsNumber, if it is numeric, the loop continues if not I remove the record with the Business Object: Collections - Action: Remove Rows.

I hope to see you helped

Best regards

------------------------------
Jhogel Ponne
Senior RPA
Ernst & Young
America/Panama
------------------------------

RE: Filtering collection

AndreyKudinov — Thu, 18 Mar 2021 08:35:00 GMT

"Here be dragons" - removing rows from collection in a loop might not work as expected.
I think Blueprism might restart the loop if dt.Length changes, but not sure atm.

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

RE: Filtering collection

VratHimbo — Thu, 18 Mar 2021 09:14:00 GMT

Thanks for your response @jhogelp.

1) How can i extract only my 5 columns?

2) If the loop might not work as expected, can be a good approach to loading into the queue and validate the item in the "validate item" page?

------------------------------
Vrat Himbo
------------------------------

RE: Filtering collection

VratHimbo — Thu, 18 Mar 2021 09:18:00 GMT

Thanks for your response @AndreyKudinov,

in your opinion, which can be the best approach to manage an excel input file --> collection before it come loaded into the queue?
My collection has an header, footer and some empty columns to be deleted.

Thanks in advance

------------------------------
Vrat Himbo
------------------------------

RE: Filtering collection

John__Carter — Thu, 18 Mar 2021 09:23:00 GMT

The Utility - Collection Manipulation.Split Collection action might help you. You provide three collection inputs - the 30 column original, a 5 column template and a 25 column template. The templates cannot be empty but a blank row is enough.

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

RE: Filtering collection

AndreyKudinov — Thu, 18 Mar 2021 09:28:00 GMT

There is a Split collection action in Collection manipulation that you can use to get only 5 columns you need.
You can use loop to delete rows, but you better do it in two passes: validate and mark rows for removal in a loop, then just filter them out.

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

RE: Filtering collection

AndreyKudinov — Thu, 18 Mar 2021 09:31:00 GMT

You can just pass same template twice, that should work too.

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

RE: Filtering collection

jhogelp — Thu, 18 Mar 2021 16:29:00 GMT

Hi,

Here is an example of the second point raised, eliminate the row inside a loop, validating a field of the collections is numeric. The collections does not restart.

You have to change the extension of the .txt file to .bpprocess

I owe you the first point for now, but what the other members are saying is a good option.

You let us know how you have done with these other approaches.

I hope to see you helped

Best regards

------------------------------
Jhogel Ponne
Senior RPA
Ernst & Young
America/Panama
------------------------------

RE: Filtering collection

VratHimbo — Fri, 19 Mar 2021 09:57:00 GMT

Hi Guys,
thanks for your support.
I've tried the bpa process and it works fine.
My bad is that i'm not able to use the split collection instead because some empty column will be added in the future in this input file.
So, can be a good approach the one below?
1) Delete empty column by column name reported in a collection
2) Remove the last *fixed number* row
3) Remove empty rows
4) Load to the queue
5) Validate Item (Is numeric?)

Thx

------------------------------
Vrat Himbo
------------------------------