Dynamic Append Fields

[RayAU]

Hi Everyone, 

I am trying to do a dynamic append fields but can't figure out how to do it in Alteryx:

The source looks like this: 

The column field determines in which column the name row belongs to. 

And desired results: 

Challenge: The numbers of columns needed to be created is not known in advanced as the source varies - so I cannot put a certain amount of Append Fields or Text to Columns tools in advance. 

How can I create the same result dynamically. 

Thank you in advance for your help. 

[jdunkerley79 · Accepted solution]

Bit fiddly, my first guess is to use an iterative macro to do the cartesian joins to make the big set with a single concatenated string value representing each column:

After this take the output and split on the separator to rows followed by a cross tab

Bit fiddly hopefully the sample package attached (macro, datafile, and sample) will help.

[patrick_digan · Accepted solution]

Nothing wrong with @jdunkerley79's method, but I used a different method to avoid concatenating. I used the iterative macro to loop through all the different possible combinations. I've attached my sample workflow using the same data.

There is always more than one way to do it!

[JohnJPS · Accepted solution]

Here's yet another approach, using the R tool:

df <- as.data.frame(read.Alteryx("#1", mode="data.frame"))
df1 <- expand.grid(split(df$Rows, df$Column))
write.Alteryx(df1, 1)

PS: credit for this goes to StackOverflow

[RayAU]

Thank you so much jdunkerley79, patrick_digan and JohnJPS for your solutions.  

All very useful for my learning and current project. 

Cheers, 

Ray