topic Filter collection on two columns in Product Forum

Filter collection on two columns

sapna_soni0290 — Tue, 30 Jun 2020 07:04:00 GMT

Hi All,

Is it possible to apply filter on a collection on two different coulmns at the same time using the single query. I have to filter one column & based on those results i want to filter another column, just like how we do manually in an excel. Is it possible via single query or I need to perform it in two different steps.

------------------------------
sapna soni
.
Capgemini Pvt Ltd
Asia/Kolkata
------------------------------

RE: Filter collection on two columns

RajathGopal — Tue, 30 Jun 2020 07:35:00 GMT

Hi Sapna,
You can filter them using a single action using the AND Operator

"[Column1] Like 'Criteria1' AND [Column2] Like 'Criteria2'"

------------------------------
Rajath Gopal
Associate IT Consultant
ITC Infotech
Asia/Kolkata
8904867411
------------------------------

RE: Filter collection on two columns

NitinPrakash — Tue, 30 Jun 2020 07:42:00 GMT

Hi Sapna,

We can filter multiple columns by adding the AND/OR between each selection Criteria.
"[Col A] = 'Data1' AND [Col B] = 'Data2' "

Regards,
NP

------------------------------
Nitin Prakash
Associate Cunsultant
Symphony Ventures
Asia/Kolkata
------------------------------

RE: Filter collection on two columns

JennaSchnizlein — Fri, 10 Jul 2020 19:34:00 GMT

How would you achieve the same thing for the Sort action within a Collection? The AND does not work. Similarly to Excel I want to sort my collection by 3 different tiers.

------------------------------
Jenna Schnizlein
------------------------------

RE: Filter collection on two columns

Yeswa_Vaibhav_1 — Mon, 13 Jul 2020 06:05:00 GMT

Hi Jenna,

For sorting collection with any number of tiers, you can use below expression.
"[Field1]='Value' AND [Field2]='Value' AND [Field3]='Value'"
(Note: If space exists in any column name you need to use [ ] to sort correctly).

You can include 'n' number of different fields to sorting values.

Please let me know if you are not clear.

Regards,

------------------------------
Yeswa Vaibhav Alwar Nerella
Associate Consultant
Capgemini
Asia/Kolkata
------------------------------

RE: Filter collection on two columns

JennaSchnizlein — Mon, 13 Jul 2020 12:42:00 GMT

Thanks, but that didn't work for me in the Sort Collection option. However, I did figure out that if you just separate them by commas that works perfectly. (Field 1, Field 2, Field 3)

------------------------------
Jenna Schnizlein
------------------------------

RE: Filter collection on two columns

david.l.morris — Mon, 13 Jul 2020 16:57:00 GMT

@Jenna Schnizlein Thank you for mentioning the use of Sort Collection to sort multiple fields. I have always simply used the Sort Collection action multiple times in a row to achieve the same thing by sorting on Field 3, then Field 2, then Field 1 which gives the same results (despite that everyone argues with me to the contrary lol).

Anyway, I should have known that was a thing. I just never tried it. Thanks!

------------------------------
Dave Morris
Cano Ai
Atlanta, GA
------------------------------

RE: Filter collection on two columns

JennaSchnizlein — Tue, 14 Jul 2020 10:51:00 GMT

@Dave Morris I thought about doing that, but couldn't figure out how to do it within the same action and if I just but them in order at the component level, it would just mess things up. Luckily Google suggested this route for me. I'm still relatively new to RPA, and finding these "simple" actions is proving to be more difficult than I would think. I'm glad this forum exists now, I'm sure I will find myself relying on it more often.

I would be interested to hear about how you got the multiple sort actions to work properly. I'm not sure how to message you directly though, so that we can breakout that conversation.

------------------------------
Jenna Schnizlein
------------------------------

RE: Filter collection on two columns

david.l.morris — Tue, 14 Jul 2020 22:47:00 GMT

But, @Jenna Schnizlein, it's a no-node platform that just anyone can pick up in a matter of hours! Hahahaha! Just kidding. There's a lot to know about even calling just the basic actions, primarily because the VBOs are just using methods from Microsoft's .NET Library. If I had looked up the code the action was using, it might have been obvious to me, but I'm lazy so I didn't. 😃

So, for the multiple sort actions... I think we're talking about the same thing here. If we're not, then I'll take it all back! 😃 Anyway, if you were trying to sort on 3 columns of a collection, you produce the same end result by sorting the collection 3 separate times but in reverse order. So, you could compare it to an Excel sort on multiple columns. There, you might tell Excel that you want to sort on multiple columns in this order: Field1, Field2, Field3. It would put alphabetical/numerical order preference on Field1, and then the next preference/sort on Field2, and then the next on Field3. In Blue Prism, if you were to do the same thing by sorting on Field1 then Field2 then Field3 with three separate action calls to Sort Collection, it would mess up your results. You'd just need to reverse it. So you sort by Field3 then Field2 then Field1, and you get the same results as if you were to use the way you found by doing Sort Collection with a field input of 'Field1,Field2,Field3'.

------------------------------
Dave Morris
Cano Ai
Atlanta, GA
------------------------------

RE: Filter collection on two columns

vinodchinthakin — Thu, 27 Oct 2022 12:18:00 GMT

Hi @Jenna Schnizlein , Could you pls guide me how to Sort Collection for Multiple conditions. For Example Filed1 needs to be Sorted as "Ascending" and Filed2 needs to be Sorted as "Descending" at once. I was able to achieve in Excel(which is called as Custom Sort). How to apply Custom Sorting in BP Collection?

Any one can suggest here.

Thanks

------------------------------
vinod chinthakindi
------------------------------