Find & Replace Value Within Collection

[ZoltánTóth]

Dear Community Members!

I have a collection with 10+ thousands rows and I need to rename some of its values. For example: "Cancellation Type A" should be "Cancellation", and "Cancellation Type B" should be "Cancellation" too. It is important that I am not looking for az exact match, for example "[Collection.Colunm] = "Result", I am looking for partial match, such as InStr([Collection.Column] = "Result" > 0. If this is True then I have to rename the value, but if it is False, then no action necessary. 

I have a working solution with a loop, but it's fairly slow with this amount of rows. That is why I have been looking for a solution to resolve this with a code stage. 

I could not find a solution within "Collection Manipulation" so I am here to ask the help of the community. 🙂

Thank you in advance!

Zoli
​

------------------------------
Zoltán Tóth
Communication specialist
Aegon Hungary Composite Insurance Company
Europe/Gibraltar
------------------------------

[ritansh.jatwani]

Hi @ZoltánTóth,

In such a scenario, rather than pasting it to collection, we can directly paste it in Excel since pasting operation would be much faster than writing collection in Excel. Once the data gets pasted in Excel, you can use code stage to perform your tasks. ​

------------------------------
Ritansh Jatwani Senior Consultant
Consultant
EY
Gurgaon
*If you find this post helpful mark it as best answer
------------------------------

[ZoltanBALINT1]

Hello Zoltan,

You would be better off if you did these manipulations in a database.
In the assumption that a DB is not an option, you can indeed try a code stage.

A crude draft in VB.net would look like this:

		' If you pass your collection as input argument, it comes in as DataTable type
		' Let's assume you called it myInputTable, and it had two columns with text fields [Customer, Cancellation type]
		
		' Here we create a new table to copy the stuff over
        Dim localTable As DataTable = New DataTable()
		localTable.Columns.Add("Customer", GetType(String))
        localTable.Columns.Add("CancellationType", GetType(String))
        

        Dim myCurrentTableRow As DataRow
		Dim tmpStr as String
        For Each myCurrentTableRow In myInputTable.Rows
			tmpStr = myCurrentTableRow.GetValue(1)
			If tmpStr.Contains("ellation") OR tmpStr.Contains("CANC") Then 
				tmpStr = "Cancellation"
			End If
            localTable.Rows.Add(myCurrentTableRow.GetValue(0), tmpStr)
		Next
			
		' you could return localTable or create a copy and decalare myOutputTable as the output collection in the code stage output parameters	
			myOutputTable = localTable​

DISCLAIMER: I did not test this, so the sample is meant just to get you going with the data types and the methods you need. Surely can google the rest.

------------------------------
Zoltan BALINT
------------------------------

[michaeloneil]

Hi Zoltan

If I'm understanding you right then what you are wanting to do is have all your data in a collection, search the entire collection for a specific term and replace all values matching that term with a new value? in that case then the below code will do that. However please note that although this will work faster than a loop query it still works through each row of a collection so with very large data volumes I'm not sure how fast it will be but this is the only way I know of doing what you want with a collection in a code stage. Please also be aware that this is using a loose search term with term greater or equal to the term you enter. If it is exact match you want then remove the > .


Inputs Collection In, Search Term, New Row Value - Outputs Collection Out
-----------------------------------------------------------------------------------
try
for each dr as datarow in Collection_In.rows
for each c as datacolumn in Collection_In.columns
if dr(c.columnname) >= Search_Term then
dr(c.columnname) = New_Row_Value
end if
next
next
Collection_Out = Collection_In
catch e as exception

end try

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

[SalmanShaik1]

Hi
can you share how to use find and replace feature in BP

------------------------------
Salman Shaik
------------------------------

[SalmanShaik1]

Hi Ritansh Jatwani,

can you share it

------------------------------
Salman Shaik
------------------------------