topic RE: Traspose in Product Forum

Traspose

VratHimbo — Thu, 23 Jul 2020 14:24:00 GMT

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

RE: Traspose

james.man — Mon, 27 Jul 2020 02:12:00 GMT

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

RE: Traspose

VratHimbo — Mon, 27 Jul 2020 07:39:00 GMT

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

RE: Traspose

VratHimbo — Mon, 27 Jul 2020 09:16:00 GMT

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

RE: Traspose

james.man — Mon, 27 Jul 2020 23:29:00 GMT

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

RE: Traspose

VratHimbo — Tue, 28 Jul 2020 07:28:00 GMT

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

RE: Traspose

james.man — Wed, 29 Jul 2020 00:29:00 GMT

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