Blue Prism Product

Product_Community.jpg
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
    ------------------------------



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



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