Transpose a collection containing collection

[PankajWalia1]

I am trying to transpose a collection that has collections for each of the columns. Using 'Transpose collection' action I am getting the error "Conversion from type 'DataTable' to type 'String' is not valid." .  
Is there a way to transpose this data structure or it has to be only text for the individual columns?

------------------------------
Pankaj Walia
Architect
Wipro
America/New_York
------------------------------

[John__Carter]

The Transpose method is intended to take 2 column collection where the 1st contains field names and the 2nd field values. It generates a new 1 row collection with filed names taken from column 1 and field values from column 2. Eg:

Column1   Column2
FirstName Pankaj
LastName Walia
Company  Wipro

FirstName LastName Company
Pankaj      Walia         Wipro

The Transpose action was not written to handle nested collections. I'm sure there will be a way to do what you want, but you need to define how the nested data structure is to be transformed into the desire result.

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

[PankajWalia1]

John, To be sure ,  I have a simple test data collection with 3 fields ie 1 collection (having 2 text fields) and 2 text items. Getting the same error 'Internal : Could not execute code stage because exception thrown by code stage: Conversion from type 'DataTable' to type 'String' is not valid.' on using 'transpose collection' action. 

If I then change the collection to all text fields transpose works . Does it mean if any of the data items in a collection is another "collection" it wont work?

Here is my real challenge - I need  to read the field names of a collection dynamically, which is why I was trying to use Transpose. 

Since transpose wont work  - Got all the collection fields using the action in 'Get collection fields"  , then i try to use from the calculation stage to read the value of the field (e.g. Table name.Field name ) , which also is not working if i use dynamic values for the Field name.

Please let me know how this can be done. 

Thanks.

------------------------------
Pankaj Walia
Architect
Wipro
America/New_York
------------------------------

[John__Carter]

Hi Pankaj

Does it mean if any of the data items in a collection is another "collection" it wont work? Correct, the transpose function isn't built to work with nested collections. If you take a look in the code stage within the utility VBO, you can see it's a pretty basic function.

not working if i use dynamic values for the Field name You can't dynamically reference a collection field by constructing an expression in a Calc.

It sounds like you may need to create a bespoke code stage that can flatten your '2D' input into a '1D' output.

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

[alexanderwebb]

Hi Pankaj Walia! 

Did you find a solution to this issue? I'm struggling with the exact same thing.

------------------------------
Alexander Webb
Automation Engineer
Ren Røros Intelligent Automation
Europe/Trondheim
------------------------------

[NicholasZejdlik]

Ultimately the problem is that DataTable columns cannot be of mixed datatypes. When you transpose, you're shifting to a two-column collection of Name and Value. Since the Value column cannot be a string for one row and a collection for another row, the Transpose action uses strings, presumably because it represent most data. (If we were using strictly .net code, one could use Object for the data type on the column, but Blue Prism doesn't support it.)

Is there a reason you're trying to transpose a collection containing a collection? If you give an example of what you're trying to do, perhaps someone could offer another solution.

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

[alexanderwebb]

Hi Nicholas, 
I discussed this with my colleague and found a solution. 
We ended up using utility to get field names in a collection, then loop through all the field names and using them as a variable to a custom C# object where we extract collection from collection. Once we have the collection we can append them to eachother and get a proper table with all the values. 

The background for this was that we got nested collections from an API and needed to handle the data. The issue was that we could get a collection containing two anywhere from 0 collections to 50 collections, without knowing the names. 

Anyways, we figured it out.

------------------------------
Alexander Webb
Automation Engineer
Ren Røros Intelligent Automation
Europe/Trondheim
------------------------------

[AmanJangid]

Hi Alexander,

I am also facing the same issue. 

Could you please share the Code which helped you resolving this issue and provide the steps to resolve it?

Thanks in advance.

--------------
Aman Jangid
--------------

------------------------------
Aman Jangid
Configuration Analyst
Fidelity International
Europe/London
------------------------------

[EVIPUTI]

You cant transpose a collection which is having nested collection . Though a indirect way can be converting the collection to JSON using Utility JSON object and than doing some manipulation for transpose and again converting JSON to collection .

------------------------------
------------------------------
Vipul Tiwari
Senior Process Simplification and Optimization Designer(Solutions Architect)
Fidelity International
------------------------------
------------------------------

[curroeva]

Hi! I have a question, were you able to solve that challenge? I have exactly the same problem as you mention here.