Splitting column data to multiple rows (without using transpose)

[Sakshi_M]

Sample data:

Index	col_a	Col_b	attr_a	attr_b	atte_c
1	Name 1	Details of 1	1,2,4	a|c|d|e	40
2	Name 2	Details of 2	3,5,6	t|g|h	20
3	Name 3	Details of 3	7,8	f|b|h	30
4	Name 4	Details of 4	9,10	k|l	15,12
5	Name 5	Details of 5	11	h	22
6	Name 6	Details of 6	22	j	42
7	Name 7	Details of 7	12,14	j	50,22

Resultant output:

Index	col_a	Col_b	attr_a	attr_b	atte_c
1	Name 1	Details of 1	1	a	40
1	Name 1	Details of 1	1	c	40
1	Name 1	Details of 1	1	d	40
1	Name 1	Details of 1	1	e	40
1	Name 1	Details of 1	2	a	40
1	Name 1	Details of 1	2	c	40
1	Name 1	Details of 1	2	d	40
1	Name 1	Details of 1	2	e	40
1	Name 1	Details of 1	4	a	40
1	Name 1	Details of 1	4	c	40
1	Name 1	Details of 1	4	d	40
1	Name 1	Details of 1	4	e	40
2	Name 2	Details of 2	3	t	20
2	Name 2	Details of 2	3	g	20
2	Name 2	Details of 2	3	h	20
2	Name 2	Details of 2	5	t	20
2	Name 2	Details of 2	5	g	20
2	Name 2	Details of 2	5	h	20
2	Name 2	Details of 2	6	t	20
2	Name 2	Details of 2	6	g	20
2	Name 2	Details of 2	6	h	20
3	Name 3	Details of 3	7	f	30
3	Name 3	Details of 3	7	b	30
3	Name 3	Details of 3	7	h	30
3	Name 3	Details of 3	8	f	30
3	Name 3	Details of 3	8	b	30
3	Name 3	Details of 3	8	h	30
4	Name 4	Details of 4	9	k	15
4	Name 4	Details of 4	9	k	12
4	Name 4	Details of 4	9	lh	15
4	Name 4	Details of 4	9	lh	12
4	Name 4	Details of 4	10	k	15
4	Name 4	Details of 4	10	k	12
4	Name 4	Details of 4	10	l	15
4	Name 4	Details of 4	10	l	12
5	Name 5	Details of 5	11	h	22
6	Name 6	Details of 6	22	j	42
7	Name 7	Details of 7	12	j	50
7	Name 7	Details of 7	12	j	22
7	Name 7	Details of 7	14	j	50
7	Name 7	Details of 7	14	j	22

Usinmg three Text to columns tool one each for the attribute field. Is there is more optimum way?

[ChrisTX]

You could create a batch macro and pass in the 3 fields to use as the source for the Text To Columns tool (split to rows).  But if you only have 3 columns, your current method without a batch macro is probably the easiest.

Chris

[Jay-RDC]

Hello. You can use Text to Columns tool to get the output you need. Please see attached screenshot. You can apply the same for the other columns. Just use the appropriate dlimeter.

[binuacs]

@Sakshi_M one way of doing this with the text tool column

[Sakshi_M]

Thanks @ChrisTX Chris. Looking for a batch macro kind of solution.. but new to this so not sure how to build this.

Also the data I am working has 5 such field out of 33 fields. Would batch macro make sense here?

[ChrisTX · Accepted solution]

@Sakshi_M see attached workflow and macro.

I avoided learning macros for a long time because they are definitely challenging.

Under Learn > Academy, try Learning Paths and Interactive Lessons and Videos.

The best advice I can offer for batch macros, which isn't stressed enough anywhere, is to feed your full data stream into the bottom input anchor, then feed your unique values (to define a batch) into the top input anchor.  In your main workflow click the icon for your macro and under the Group By tab, for the Control GroupBy Field drop-down, choose the field name that has your unique values (to define a batch). For the field Input GroupBy Field drop-down, leave that value as [None].

To debug a macro, open your macro then use menu option View > Interface Designer, then click the magic wand icon, populate any parameter values, then click the Open Debug window. A debug workflow will open as a new tab in your current Alteryx session. Run the debug workflow to make sure it is creating your desired results.

This also helps to debug:
Community > Blogs > Engine Works Blog > The X-Ray Browse: Macro Debugging Made Easier
https://community.alteryx.com/t5/Engine-Works-Blog/The-X-Ray-Browse-Macro-Debugging-Made-Easier/ba-p...

Chris

[Sakshi_M]

Thanks @ChrisTX This is very informative. ! (y)