topic Re: Data Collection 'V-lookup' to create 1 master collection in Product Forum

Data Collection 'V-lookup' to create 1 master collection

whitehouse-UoL — Thu, 04 Jul 2024 10:30:47 GMT

Hi All,

Please can someone help?

I am trying to create a data collection to add to a work queue which contains all the data I require to complete my workflow.

The data I start with is this:

Created from the system I am working in. Each option in the ‘Recommendation’ column/field has a corresponding code defined below.

What I would like to achieve is a data collection that looks like this at the end. Which I can then pass into a work queue to work from.

Thank you!

Re: Data Collection 'V-lookup' to create 1 master collection

rokkam_saiteja — Thu, 04 Jul 2024 11:46:30 GMT

Hi @whitehouse-UoL

Try this

After merging you can remove unwanted column from the main collection by following these steps

in decision box [Collection Fields.Field Name]="not req column name"

Thanks

Re: Data Collection 'V-lookup' to create 1 master collection

whitehouse-UoL — Thu, 04 Jul 2024 12:59:39 GMT

Hi @rokkam_saiteja
Thank you for your reply. I have given this ago and I am not getting the results I need. This just seems to be adding the to collections together.

I need the information in the new collection to contain all the IDs and recommendations and have the Code and Comment column generated on the recommendation column.

Thanks.

Re: Data Collection 'V-lookup' to create 1 master collection

rokkam_saiteja — Thu, 04 Jul 2024 13:25:29 GMT

Merge all collection to Main Collection Keep IDs, recommendations and coding fields and delete the other.

then use append field action, mention field name then u can see this below

based on the recommendation column value u can set the comment by using calculation stage to the copy and paste field in main collection

Thanks

Re: Data Collection 'V-lookup' to create 1 master collection

Mohamad_Dakkouri — Thu, 04 Jul 2024 17:08:53 GMT

Hi @whitehouse-UoL

Try to use VBO Millicom

https://digitalexchange.blueprism.com/dx/entry/35840/solution/millicon---collection-utility

You have the action group by.

Let me know if it's is worked for you 🙂

Re: Data Collection 'V-lookup' to create 1 master collection

devneetmohanty07 — Mon, 08 Jul 2024 09:36:22 GMT

Hi @whitehouse-UoL ,

Since you are not able to find any solutions yet, I would like to provide you with a custom code stage-based solution that you can implement by extending the Utility - Collection Manipulation VBO. You can simply add another action page to this business object called as Join Collection and then provide three input arguments called as:

- In_CollectionOne (Type: Collection)

- In_CollectionTwo (Type: Collection)

- In_CommonFieldName (Type: Text)

Map these data items to the Start stage for making them our input arguments:

Now, you can add another data item called as Out_Collection (Type: Collection) and map this data item to End Stage for making it our output argument:

Now, add a code stage and map these data items to the code stage and use the following code in the Code tab:

' Clone structure of In_CollectionOne To Out_Collection: Out_Collection = In_CollectionOne.Clone() ' Add columns To Out_Collection from In_CollectionTwo that are not in In_CollectionOne: For Each column As System.Data.DataColumn In In_CollectionTwo.Columns If Not Out_Collection.Columns.Contains(column.ColumnName) Then Out_Collection.Columns.Add(column.ColumnName, column.DataType) End If Next ' Merge rows from In_CollectionOne and In_CollectionTwo In Out_Collection: For Each rowA As System.Data.DataRow In In_CollectionOne.Rows Dim newRow As System.Data.DataRow = Out_Collection.NewRow() Out_CurrentRow +=1 ' Copy all columns from In_CollectionOne: For Each column As System.Data.DataColumn In In_CollectionOne.Columns newRow(column.ColumnName) = rowA(column.ColumnName) Next ' Check if there are matching rows in In_CollectionTwo: Dim key As String = rowA(In_CommonFieldName).ToString() Dim foundMatch As Boolean = False For Each rowB As System.Data.DataRow In In_CollectionTwo.Rows If rowB(In_CommonFieldName).ToString() = key Then foundMatch = True ' Copy columns from In_CollectionTwo that are not in In_CollectionOne: For Each column As System.Data.DataColumn In In_CollectionTwo.Columns If Not In_CollectionOne.Columns.Contains(column.ColumnName) Then newRow(column.ColumnName) = rowB(column.ColumnName) End If Next Exit For End If Next ' Add New Row Item To Out_Collection: Out_Collection.Rows.Add(newRow) Next

Once, this has been built, publish the action and save the business object for us to test it on Process Studio.

Testing In Process Studio:

For my example, I have taken two collections namely, Application Details and Rejection Details which follow the same data structure and values that you initially provided in your screenshot.

Now, the first thing we need to do is to make sure that the column name for the common columns in both the collection which in our case are the Recommendation and Assessment Recommendation should be same for our newly created action to work. For this reason, I will use the Rename Field action from the Utility - Collection Manipulation VBO and will pass the values to rename the column name for Assessment Recommendation column in Reject Details collection to Recommendation and store it in a new collection called Rejection Details Renamed:

Once, this has been done, we will end up with the following collection:

Now, we will us our newly created action, Join Collection from Utility - Collection Manipulation while passing the Application Details and the newly created Rejection Details Renamed collections with the common field name as Recommendation and store the results in a new collection called Final Collection:

Once the action has been executed, we will have our desired data in the Final Collection as follows:

Re: Data Collection 'V-lookup' to create 1 master collection

whitehouse-UoL — Wed, 10 Jul 2024 12:15:10 GMT

Hi @Mohamad_Dakkouri
Thank you for your suggestion. This utility did the job perfectly! I did have to run my info through it twice to get the two columns to fill but other than this perfect. Thanks you!

Re: Data Collection 'V-lookup' to create 1 master collection

whitehouse-UoL — Wed, 10 Jul 2024 12:16:43 GMT

Hi @devneetmohanty07
Thank you so much for your detailed response. I managed to get the solution I needed from one of the other post.