Spliting single row to multiple rows

[Nidhin]

Hi

I have a requirement to split a single row to multiple rows by selecting n columns for each row

In the example I am splitting the source data into multiple rows by selecting every 5 columns

Input

Value 1

Value 2

Value 3

Value 4

Value 5

Value 6

Value 7

Value 8

Value 9

Value 10

Value 11

Value 12

Value 13

Value 14

Value 15

Value 16

Value 17

Value 18

Value 19

Value 20

Value 21

Value 22

Value 23

Value 24

Value 25

Output

Value 1	Value 2	Value 3	Value 4	Value 5
Value 6	Value 7	Value 8	Value 9	Value 10
Value 11	Value 12	Value 13	Value 14	Value 15
Value 16	Value 17	Value 18	Value 19	Value 20
Value 21	Value 22	Value 23	Value 24	Value 25

I tried with Dynamic select and setting Field Number between a range. It works for small data set

But for larger sets its con ideal.

Is there a way to do this dynamically using any tools in Alteryx

Thanks in Advance

[AngelosPachis · Accepted solution]

Hi @Nidhin ,

One way to do it would be by using the make columns tool. At first you have to transpose your table to have two columns, and then in the Make columns tool you decide the total number of columns you will need (in this instance 5)

Hope that helps,

Angelos

[atcodedog05]

Hi @Nidhin 

Here is how you can do it.

Workflow:

1. Using transpose tool to convert columns to rows.

2. Using record id tool to set row id (column id).

3. Using formula tool to set number of columns. Using set number of columns grouping record id into columns and rows.

4. Using crosstab tool with row as key, column as name and value as value converting it into a table.

Hope this helps 🙂

[atcodedog05]

Make column tool approach Nicely done @AngelosPachis 😀👍

I need to start using that tool more often.

[Pingu]

The tile cool can also do with what you did with all the formulas atcodedog05. Its very handy. I was going to post a solution as well, but seems redundant now!

[AngelosPachis]

@atcodedog05 it's a cool one, isn't it? I remember I picked this trick up from one of @Jonathan-Sherman 's posts.

[Nidhin]

@AngelosPachis 

I used Dynamic Select in Batch Macro to to select 5 columns at a time and recreate the Table.

Your solution is more cleaner and elegant.

The make column tool is in which Tool Palette ? I assumed it to be in "Transform". But couldn't find it there

Thanks

Nidhin

[atcodedog05]

Hi @Nidhin 

Yes, make columns is amazing. Its in laboratory pallete 🙂

[AngelosPachis]

@Nidhin what @atcodedog05 just said, you can find it in the Laboratory tool palette 👍