Lining up disjointed columns within same dataset?

[CGIQV]

I have a report that I'd like to transform so that it can be batch imported to our software.

The report looks something like this:

Name: Charlie
Exercise	Light	Medium	Hard
Run	15	10	2
Jump	20	10	2
Swim	20	5	5
Name: Becky
Exercise	Light	Hard
Run	30	12
Jump	30	12
Swim	25	12

I need this:

Name	Exercise	Light	Medium	Hard
Charlie	Run	15	10	2
Charlie	Jump	20	10	2
Charlie	Swim	20	5	5
Becky	Run	30		12
Becky	Jump	30		12
Becky	Swim	25		12

Using the multi-row formula I've been able to create the name column:

IF Contains([Field1]],"Name:") THEN trim(Replace([Field1],"Name:",""))
ELSE [Row-1:Name] ENDIF

This adds a column and layers in the name until a new name appears.  But I can't seem to figure out how to work with the gap in columns.  When one person doesn't have data in a specific column it is omitted, but I need them all to lineup as shown.

Suggestions?

[CGIQV]

Basically I solved up to here with my own method already:

But conceptually I still don't understand how the macro is combining the data from the individuals even though they are in different columns.

[mceleavey]

Ok, so you can right click on the macro and select "Open Macro":

This will show you the following:

So the input is the input from the stream, and the control parameter is the field we're going to use as the batch (the field that we'll loop through). This then updates the filter value, which in effect means we'll filter by the first the name on the list, then format the data, then we'll go to the second name on the list, format the data and union to the first name and so on until all names are processed.

This is called a batch macro, and you can read more about how they work here:

https://help.alteryx.com/2018.2/BatchMacro.htm

Hope this helps.

M.

[CGIQV]

Thanks for the explanation and link.

I'll see if I can work through this and report back.

[CGIQV]

Definitely understanding this better now.

I've manipulated some data and still gotten it to work.  I don't know to skip the first two rows so I took that out of the macro.

My problem is the size of the data set.  When I change from the current 2 row 5 column structure, I end up getting no results beyond the macro.  Do I need to modify the input on the macro beforehand so that the template shows exactly how many rows there will be?  how would this work if the input file constantly had a different number of names?

[mceleavey · Accepted solution]

Without seeing the data I'm afraid I can't help any further.

If you can provide me with the data I can build it for you and explain how it works.

M.

[CGIQV]

Was about to shoot over a different data set, but I ran it through the workflow and it worked without an issue!

I must have tinkered with someone accidentally so I re downloaded the package and went from there.  Went without a hitch.  More importantly, it makes sense now so I should be able to reproduce in the future.

Thanks again for the help.

[mceleavey]

Fantastic!

No problem. Glad I could help.

M.