- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
12-11-20 03:41 PM
I have 2 collections that I want to filter:
1st collection: I want to filter by the unique value to avoid duplicates. I want to filter by name and number, but I don't know how I should filter by unique value, should I add also the action of collection contains value? I tried but I can do it with just one column and I want to filter both of the columns, name and number to be in line.
2nd collection: I want to filter by the blank rows, when the value of the first column is blank, take the value of the second column.
In this case, I don't know how I can filter with blank row. I tried the following expression but it didn;t work:
"Name = ''"
"Name=" & ""
Any ideas on how I should try in this situations?
Thank you,
------------------------------
Madalina Acsinte
------------------------------
Answered! Go to Answer.
Helpful Answers
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
13-11-20 12:51 PM
For 2nd collection: For Filtering use like this: "Name = ''" (i.e., after = symbol place two single quotes (') and then one double quoted (")) in Collection Manipulation VBO- Filter Collection Action.
By using this you will get entire rows with Name field is having empty value in to another collection. Using loop stage you can get each row values into calculation stage if required.
Note: No need to provide empty space to search for empty fields.
For 1st collection: There is no direct VBO/Action to remove duplicate rows in collection. We need to write code to perform this action. But I found one useful discussion form. please refer this it might be useful. https://community.blueprism.com/communities/community-home/digestviewer/viewthread?GroupId=145&MID=201&CommunityKey=3743dbaa-6766-4a4d-b7ed-9a98b6b1dd01&tab=digestviewer
Please let me know if you need more assistance.
------------------------------
Yeswa Vaibhav Alwar Nerella
Associate Consultant
Capgemini
Asia/Kolkata
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
13-11-20 12:51 PM
For 2nd collection: For Filtering use like this: "Name = ''" (i.e., after = symbol place two single quotes (') and then one double quoted (")) in Collection Manipulation VBO- Filter Collection Action.
By using this you will get entire rows with Name field is having empty value in to another collection. Using loop stage you can get each row values into calculation stage if required.
Note: No need to provide empty space to search for empty fields.
For 1st collection: There is no direct VBO/Action to remove duplicate rows in collection. We need to write code to perform this action. But I found one useful discussion form. please refer this it might be useful. https://community.blueprism.com/communities/community-home/digestviewer/viewthread?GroupId=145&MID=201&CommunityKey=3743dbaa-6766-4a4d-b7ed-9a98b6b1dd01&tab=digestviewer
Please let me know if you need more assistance.
------------------------------
Yeswa Vaibhav Alwar Nerella
Associate Consultant
Capgemini
Asia/Kolkata
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
16-11-20 09:33 AM
to remove completely duplicate row,
and write code in the code stage:
utCollection=inCollection.DefaultView.ToTable(true)
And I'm having the same problem (remove rows with duplicate values).
I try one solution, I wrote it on this page: https://qiita.com/Sumire_Neko/items/26b9d146ab67eaa7ddcf
(This is written in Japanese, so please read it using a translation tool or something)
To filter blank value, how about using len([Column Name])<1 instead of "Name=" & ""
My suggestion may not be helpful, I also want to know how to avoid duplicate values.
------------------------------
Mitsuko
Asia/Tokyo
------------------------------
Mitsuko
Asia/Tokyo
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
22-12-21 09:52 PM
Bit late to the party, but came across this question and was curious if another method for filtering the blank rows would work so tested it and thought I'd reply here in case it's useful to anyone else.
As the collection filter expression is essentially an SQL query that gets applied to the collection contents, you can use some functions within the expression as well. In this case the requirement was to filter the collection for blanks in the Name column. So, another way to check for an empty string in the filter expression is to use the string Length function as shown below.
Sample input collection with random blanks in Name column:
Filter expression using Len function to check Name column:
Sample output with only the blanks:
Avoids going cross-eyed trying to figure out what combination of double quotes and single quotes you need/have in your filter expression 😋
------------------------------
Jared Rumball
Q4 Associates Ltd
https://www.q4associates.biz/
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
22-12-21 10:05 PM
------------------------------
Jared Rumball
Q4 Associates Ltd
https://www.q4associates.biz/
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
23-12-21 02:14 AM
Ideally I think this happens because the filter expression most likely doesn't recognize the Blue Prism functions but it works as per the Select method argument of Datatable class in C# or VB .NET framework. It should accept values like:
CONVERT
– converts particular expression to a specified .NET Framework typeLEN
– gets the length of a stringISNULL
– checks an expression and either returns the checked expression or a replacement valueIIF
– gets one of two values depending on the result of a logical expressionTRIM
– removes all leading and trailing blank characters like \r, \n, \t, ‚ 'SUBSTRING
– gets a sub-string of a specified length, starting at a specified point in the stringAND
,OR
andNOT
LIKE
,IN
= <> < <= > >=
SUM,COUNT,MIN,MAX,AVG(average),STDEV
(statistical standard deviation) andVAR
(statistical variance).
<DataTable_object>.CaseSensitive = False
------------------------------
----------------------------------
Regards,
Devneet Mohanty
Intelligent Automation Consultant
Blueprism 6x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
Hope this helps you out and if so, please mark the current thread as the 'Answer', so others can refer to the same for reference in future.
Regards,
Devneet Mohanty,
SS&C Blueprism Community MVP 2024,
Automation Architect,
Wonderbotz India Pvt. Ltd.
