topic Re: Adding a column and populating the Data into that column in Product Forum

Adding a column and populating the Data into that column

Babjee24 — Mon, 22 Apr 2024 15:26:39 GMT

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.

Re: Adding a column and populating the Data into that column

Daniel_Sanhueza — Mon, 22 Apr 2024 16:06:29 GMT

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.

Re: Adding a column and populating the Data into that column

Babjee24 — Mon, 22 Apr 2024 17:36:46 GMT

Hi @Daniel_Sanhueza

Thanks for the reply.

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

Re: Adding a column and populating the Data into that column

harish.mogulluri — Mon, 22 Apr 2024 18:58:36 GMT

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

Re: Adding a column and populating the Data into that column

Daniel_Sanhueza — Mon, 22 Apr 2024 20:46:43 GMT

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?

Re: Adding a column and populating the Data into that column

Babjee24 — Tue, 23 Apr 2024 01:33:37 GMT

Hi @Daniel_Sanhueza ,

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

Re: Adding a column and populating the Data into that column

Daniel_Sanhueza — Tue, 23 Apr 2024 02:28:51 GMT

I see @Babjee24 as @harish.mogulluri 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,

Re: Adding a column and populating the Data into that column

MichealCharron — Fri, 03 May 2024 12:27:17 GMT

@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

Re: Adding a column and populating the Data into that column

Daniel_Sanhueza — Fri, 03 May 2024 17:33:09 GMT

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 🙂