Transfer columns down

[ChristosN]

Hello

I am trying to create a flat file from a huge amount of raw data and I want to transfer columns down.

Eg:

Input:

Col1 Col2 Col3 Col4 Col5 Col6 Col7

a       2      3      4       5         4       4

b       3      4      5       6         5       2

c       2      3      4       5         8       9

d       2      3      7       5         1       1

e       0      9      8       7         0       8

Output:

Col1 Col2  Col3

a        2       3

b        3       4

c        2       3

d        2       3

e        0       9

Col1 Col4 Col5

a        4        5

b        5        6

c        4        5

d        7        5

e        8        7 

Col1 Col6 Col7

a        4        4

b        5        2

c        8        9

d        1        1

e        0        8

[Jonathan-Sherman]

Hi @ChristosN,

We could achieve this by using a batch macro to identify each "group" of columns we wanted to keep. The batch macro will then union all "groups" together.

Workflow:

Macro:

Output Achieved:

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[DavidP · Accepted solution]

Below is how I would do it. Note that you have to load the data in the format shown (where Column Name is in the 1st Row)

[ChristosN]

Hello David and thank you for your response.

I am trying to analyze a 23 column (by 156 rows) data-set.

Unfortunately I cannot understand the logic of your algorithm, so I cannot apply that workflow to my data. 

I would be very grateful if you could send me a workflow for the following

Input: (Column name is in the first row)

Col1 Col2 Col3 Col4 Col5 [...] Col22 Col23

a        a       9       7      2             1        0

b        s       5       1      9             3        7

c        d       1       9      1             0        9

Output:

Col1 Col2 Col3

a        a      9

b        s      5

c        d      1

Col1 Col2 Col4

a        a      7

b        s      1

c        d      9

Col1 Col2 Col5

a        a      2

b        s      9

c        d      1

[...]

Col1 Col2 Col22

a        a      1

b        s      3

c        d      0

Col1 Col2 Col23

a        a      0

b        s      7

c        d      9

[DavidP · Accepted solution]

Hi @ChristosN 

Please find updated workflow attached - I've added some documentation.

In your new requirement, you're replicating the 1st 2 fields to be replicated each time, so in the transpose tool, you pick field1 and 2 as key fields.

The next section deals with how many columns you want to show after the 1st 2 - in this case it's only 1 so the value is always 1.

Based on the previous info, the Tile and CrossTab tool creates Column 3.

Finally the sort tool makes sure everything is in the right order and then you can remove the Tile Tool's fields.

Let me know if you have any questions.

[ChristosN]

Hi David

It worked perfectly.

Thank you for your time. The documentation helped me understand the workflow.

Have a nice day,

Christos