Transpose Collection (Vertical to Horizontal) not available

[Tejaskumar_Darji]

Hello all,

We have function in Collection Utility to covert Horizontal collection to Vertical collection.

I am looking for doing the same reverse. Like i have vertical collection which needs to be transposed to horizontal collection. (So that i can reference data item using the header title of the field)

I looked everywhere but could not find a solid solution for this.

Isn't this something very important to be added in the out of the box Collection Utility?

Also if someone know solution for this let me know.

thanks,
Tejas

------------------------------
Tejaskumar Darji
------------------------------

[NicholasZejdlik]

Assuming you have something like this:

And you want something like this:

There are two solutions. If you want to avoid a code stage, loop over the original collection and use the Append Field action to append each row as a new column on a blank collection.

If you'd prefer a code stage, this should work. Input has Collection_In, and Output has Collection_Out.
' Ensure we have the proper number of columns. If Collection_In.Columns.Count <> 2 Then Throw New Exception("Collection_In must have exactly two columns") End If ' Add the column names to the output collection. For Each Row As DataRow In Collection_In.Rows Collection_Out.Columns.Add(Row(0), Collection_In.Columns(0).DataType) Next ' Add the values to the output collection. Dim NewRow As DataRow = Collection_Out.NewRow() Dim Count As Integer = 0 For Each Row As DataRow In Collection_In.Rows NewRow(Count) = Row(1) Count += 1 Next Collection_Out.Rows.Add(NewRow)​

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

[MadhurChopra]

Hey Tejas,

I agree with Nicholas' solutions. Bear in mind though that the code stage does require to define the number of columns you want to transpose to. If you look at the code sample Nicholas has provided, you will also understand why a vertical to horizontal transpose isn't quite as simple as horizontal to vertical. Given the way traditional databases have been designed, most programming languages use column definitions as the record structure and therefore make it easier to add rows (records) vs. adding columns. Now if you were doing this in Excel, it appears quite simple owing to the way it is designed; in-memory storage structures such as collections and traditional databases aren't quite as simple unfortunately.

Please let us know how the application of the two approached go.

Regards.

------------------------------
Madhur Chopra
Sr. Product Consultant
Blue Prism - Professional Services
America/Los_Angeles
------------------------------

[NileshJadhavBP]

Hi Tejaskumar,

I am agree with previous all answer and post.

If you are taking data from excel to collection and try to transpose then you have to follow very long method and time consuming if data is large (using loop)

write macro to get data and transpose it and after that you can take into collection.

I hope it might help you.

Thanks
Nilesh

------------------------------
Nilesh Jadhav
Senior RPA Specialist
------------------------------

[Tejaskumar_Darji]

Thanks for explanation.

I was trying to achieve this with Append Field but did not came as expected. Can you help me what value to put in the input property here for Field name and Value. Because we can not reference the header of collection here to loop over if it is vertical. Need Help.

Thank you
Tejas

------------------------------
Tejaskumar Darji
------------------------------

[Tejaskumar_Darji]

Hi Nicholas,

Can you help me with the above query for the same.

Thanks.

------------------------------
Tejaskumar Darji
------------------------------

[jebarson]

To transpose the collection. Follow the below steps. 

1) Create a single row collection to store output value's.
2) Loop through the input collection & use "Append Field" action to add new column name & write respective value in output collection.
2) Pass the header name (Coll1.Name) & value (Coll1.Value) references as input parameters for "Append Field " action.

Field Name ->  Coll1.Name
Field Value ->   Coll1.Value

------------------------------
Jane Jebarson Senior Automation Engineer
Engineer
Allianz
Europe/London
------------------------------