Traspose

[VratHimbo]

Hi team,
i'm new here!
I've used the "traspose collection" action (Utility - Collection Manipulation) from my FIRST COLLECTION to the SECOND COLLECTION.
Now i want, from the SECOND COLLECTION, create a THIRD COLLECTION with the same value of the first one.
I try to loop and use a calculation stage to store the result, but i'm not able to do this, some advice please?

The second collection have the name of column in the first column and value in the second column
FIRST COLUMN   SECOND COLUMN
Name                      Frank
Surname                 Zappa
ID                            1
Name                      Mark
Surname                 Chin
ID                            2

Thanks in advance

------------------------------
andrea alfani
------------------------------

[james.man · Accepted solution]

If you mean that you want to "untranspose" Collection 2 so that it turns back into Collection 1, you'll need to do a lot of calculations.

At a high level:
1. Figure out how many columns you have for Collection 2 and add them as fields in Collection 3
2. Loop over Collection 2 and write them to a new columns of Collection 3.  When you see that you've written enough columns in Collection 3, add a new row in Collection 3 and repeat

I've attached an example of this.  Note that this only works for purely text fields.

------------------------------
James Man
Professional Services
Blue Prism
Asia/Hong_Kong
------------------------------

[VratHimbo]

Yes, that's exactly what I was looking for.
I'm going to study the logic behind for a better understanding.
Just in case i haven't the column name how can i do the same thing but generically? So, without the collection.name for example?
Really appreciate your help 🙂
Have a nice day

------------------------------
andrea 
------------------------------

[VratHimbo]

Can i ask you why in the action "Collection Contains field" and then "Has field" it pass only 3 times instead of 6 times like the rows of collection 2?

Thanks @james.man ​

------------------------------
andrea alfani
------------------------------

[james.man]

It's because for Collection 2 we know that the rows will eventually repeat:
FIRST COLUMN   SECOND COLUMN
Name                      Frank
Surname                 Zappa
ID                            1
Name                      Mark
Surname                 Chin
ID                            2

So if I want to know how many fields/columns are in the un-transposed collection, I only need to read "FIRST COLUMN" until I see that there is a repeating value.

For the Collection 2 loop:
Loop Row 1: Does the un-transposed collection have the "Name" column?  No, so add it as a field/column
Loop Row 2: Does the un-transposed collection have the "Surname" column?  No, so add it as a field/column
Loop Row 3: Does the un-transposed collection have the "ID" column?  No, so add it as a field/column
Loop Row 4: Does the un-transposed collection have the "Name" column?  Yes, so we know that we've already added all of the columns, and we can end the loop over Collection 2

------------------------------
James Man
Professional Services
Blue Prism
Asia/Hong_Kong
------------------------------

[VratHimbo]

Thanks for your help @james.man now it's totally clear.
Last question: can i iterate a collection through index column or only with column name? In that case, can i build (coding) it?

Have a nice day,
AA​

------------------------------
andrea alfani
------------------------------

[james.man]

There's no direct way of using an index for your column instead of a column name.  But it is always possible to use the "Utility - Collection Manipulation" VBO "Get Collection Fields" action.  The result of that will give something like this:

Then you can use this "column name collection" to perform looping

------------------------------
James Man
Professional Services
Blue Prism
Asia/Hong_Kong
------------------------------