topic RE: Filter collection by duplicates and blank rows in Product Forum

Filter collection by duplicates and blank rows

MadalinaAcsinte — Thu, 12 Nov 2020 15:41:00 GMT

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

RE: Filter collection by duplicates and blank rows

Yeswa_Vaibhav_1 — Fri, 13 Nov 2020 12:51:00 GMT

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

RE: Filter collection by duplicates and blank rows

sumire — Mon, 16 Nov 2020 09:33:00 GMT

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

RE: Filter collection by duplicates and blank rows

Jared.Rumball — Wed, 22 Dec 2021 21:52:00 GMT

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

RE: Filter collection by duplicates and blank rows

Jared.Rumball — Wed, 22 Dec 2021 22:05:00 GMT

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

RE: Filter collection by duplicates and blank rows

devneetmohanty07 — Thu, 23 Dec 2021 02:14:00 GMT

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

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