Blue Prism Product

last person joined: yesterday 

Chat about Blue Prism products
Back to discussions
Expand all | Collapse all

Transpose a collection containing collection

  • 1.  Transpose a collection containing collection

    Posted 08-11-2020 11:26
    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
    ------------------------------


  • 2.  RE: Transpose a collection containing collection

    Posted 08-12-2020 04:22
    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
    ------------------------------

    Original Message


  • 3.  RE: Transpose a collection containing collection

    Posted 08-12-2020 10:31
    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
    ------------------------------

    Original Message


  • 4.  RE: Transpose a collection containing collection

    Posted 08-12-2020 13:57
    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
    ------------------------------

    Original Message


  • 5.  RE: Transpose a collection containing collection

    Posted 11-25-2020 04:26

    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
    ------------------------------

    Original Message


  • 6.  RE: Transpose a collection containing collection

    Posted 11-25-2020 10:47
    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
    ------------------------------

    Original Message


  • 7.  RE: Transpose a collection containing collection

    Posted 11-26-2020 03:17
    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
    ------------------------------

    Original Message


Share