Filter collection by duplicates and blank rows

[MadalinaAcsinte]

Hi,

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
------------------------------

[Yeswa_Vaibhav_1 · Accepted solution]

Hi Madalina,

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
------------------------------

[sumire]

Hello,

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
------------------------------

[JaredRumball]

Hi,

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/
------------------------------

[JaredRumball]

p.s. Len and Trim seem to be the only string functions that BP will recognise in the filter collection expression, doesn't allow others I've tried so far like Left, InStr, Upper etc.

------------------------------
Jared Rumball
Q4 Associates Ltd
https://www.q4associates.biz/
------------------------------

[devneetmohanty07]

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 type
• LEN – gets the length of a string
• ISNULL – checks an expression and either returns the checked expression or a replacement value
• IIF – gets one of two values depending on the result of a logical expression
• TRIM – 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 string
• AND, OR and NOT 
• LIKE , IN
• = <> < <= > >=
• SUM,COUNT,MIN,MAX,AVG(average),STDEV(statistical standard deviation) and VAR(statistical variance).
  

Unfortunately for lower or upper operator we don't have any specific function as such. the workarounds to achieve a comparison without any case sensitivity would be to use the select query in the code stage for the Filter Collection after the expression: <DataTable_object>.CaseSensitive = False

------------------------------
----------------------------------

Regards,
Devneet Mohanty
Intelligent Automation Consultant
Blueprism 6x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------