Filter Advanced

[Stella_111]

Hi Some one can help me with this ?

Notice i dont want to use a loop or a CUSTOM Code. 

I am using filter expression : 

"IIF([Sas] LIKE '%"&Chr(10)&"%', REPLACE([Sas],'"&Chr(10)&"','') , [Sas]) = 'Foreign'"

To explain i have a column Sas that contain Foreign but we have an issue we have a Chr(10) in the column i mean it can be F&Chr(10)&oreign or Foreign&Chr(10) Or sometimes Chr(10)&Foreign or Chr(10) can be in the middle. Any idea ?

Thank you 

[Nandhakumar]

Hi Stella,

Have you tried the below filter exception?

REPLACE([SAS], Chr(10),'')="Foreign"

Thanks,

Nandha

[Stella_111]

@Nandhakumar 

Thank you so much for your answer.

Unfortuneltly this expression is not allowed in Filter...

Any other idea ?

[david.l.morris]

I don't think it is possible to achieve that using the Column Expression syntax that the Filter Collection uses. Technically non-robust versions could be achieved, but I don't see the point in that.

Why are you not wanting to use a loop or custom code?

If you absolutely must use no loop and also no custom code, then I would suggest trying something like this:

1 - Use Utility - JSON to convert your collection to JSON and save to a text data item

2 - Use a calc stage with the Replace() function to remove all instances of Chr(10).

3 - Use Utility - JSON to convert the JSON back to a collection.

There are of course reasons what I said above may not work for you, but I would think that should achieve what you want.

I don't know whether you are trying to specifically remove only Chr(10) when it shows up before, in the middle of, or at the end of "Foreign" though. If that's the case where you want to keep all other Chr(10), then we could think through that.

[Stella_111]

Hi @david.l.morris 

Thank you very much for your answer 

In this web page : 

https://community.blueprism.com/t5/Product-Forum/HOW-TO-USE-FILTER-ON-A-COLLECTION-STAGE/m-p/116835#M52298 

I saw that we can do a lot of things with filter expression. I am wondering if we can do the Replace function in the filter expression...

[david.l.morris]

Hi @Stella_111 ,

This is Microsoft's documentation on the Data Column Expression syntax:

https://learn.microsoft.com/en-us/dotnet/fundamentals/runtime-libraries/system-data-datacolumn-expression 

The problem is that there is no Replace() function supported in that syntax. There is also no InStr() function in the syntax either, which I find a bit weird. This means you effectively have no way to blanket replace stuff, and there is no way to identify where in a string some character is in order to dynamically replace that part of a substring.