Adding a column and populating the Data into that column

[Babjee24]

Hi All,

I've two collections with data in them, now I create a new column in collection1  and without looping in I want to fill the data in the newly created column in such a way that Collection1.Coumn1&Collection2.column1 is the data that is expected in that.

[Daniel_Sanhueza]

Hey @Babjee24 

Have you used Utility - Collection: Merge Collection? As long if both collection as the same number of rows you shouldnt have problems. If both collection are not equals in rows count, then the result could not be as expected, in that scenario I would use Excel as pivot, write bot collection and the obtaining the full range.

[Babjee24]

Hi @Daniel_Sanhueza 

Thanks for the reply.

After merging the collections how do we append the columns without looping the collection.

[harish.m]

HI Babjee,

 you can write code stage in C#  but still you need to do some kind of loop to achieve this
If you have large data set using C# or vb.net is the best option to my knowledge

[Daniel_Sanhueza]

Just to clarify and see if I understand correctly,

You have Collection1

First Name

Daniel

Sergio

Gloria

And Collection2

Last Name

Sanhueza

Balmaceda

Fuentes

If you merge, you'll have

First Name	Last Name
Daniel	Sanhueza
Sergio	Balmaceda
Gloria	Fuentes

but also want to add a third column and expect the result on each cell value will be First Name&Last Name but want to accomplish this without looping? 

First Name	Last Name	Full Name
Daniel	Sanhueza	Daniel  Sanhueza
Sergio	Balmaceda	Sergio  Balmaceda
Gloria	Fuentes	Gloria  Fuentes

Am I understanding wrong? 

[Babjee24]

Hi @Daniel_Sanhueza ,

Yes, I want the Full Name column to be created and updated exactly the same way without looping.

[Daniel_Sanhueza]

I see @Babjee24 as @harish.m I'm pretty sure that it can be done with some sort of code. However, I'm concerned about memory usage. What if you need to handle a large amount of data? Well, you'll have to chunk the data into small collections that can be managed, I know. But still, you're using BP to manage that amount.

Perhaps this is personal, but I dont like to manage memory like that. Usually I just use excel to take care of memory usage and using BP to send actions to work the data and when it is all sorted, get the worksheet as I need. 

Having said that, FullName Column is the result of a formula in excel that were expanded.

=A2&" "&B2

In Blue Prism, you can achieve this by using actions such as 'Set Cell Value' to send the formula, followed by 'Copy and Paste Worksheet in Range', where the source value is the cell containing the formula. The destination is the same worksheet, but you need to specify the range of the entire table. This should paste the formula into each cell.

I don't recall if when you retrieve the collection, it brings the formula instead of the value. If it does, simply copy and paste values only.

Perhaps someone has attempted a different approach 🙂

Regards,

[MichealCharron · Accepted solution]

@Babjee24 

Blue Prism created an action in the Utility - Collection Manipulation VBO called Append Calculated Field which can append a field based on a calculation of other fields in your collection. Check out the documentation on it.
Utility - Collection Manipulation - 10.2.0 

[Daniel_Sanhueza]

Always on point @MichealCharron! I didnt have the chance to update the VBO and right now I'm thinking on several bots that were developed on v6.5 that could benefit of the new actions that were updated.

Thank you 🙂