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

Workflow

Datasets

Developer Tools

Tips and Tricks

Common Use Cases

Help

Preparation

Data Investigation

Accepted answers

DavidP

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)

Transfer Columns down.yxmd

DavidP

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.

Transfer Columns down.yxmd

All comments

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.