Join rows with different set of columns (Schema) after each iteration

[GaRaGe]

I am running a batch macro in my Alteryx workflow which gives 1 row as an output with different set of fields in each iteration. I need to append all these rows together by creating a new column, if required, after each iteration. Those rows which do not have values for the corresponding column should be replaced by 0. 

Any idea how to do it?

MACRO OUTPUT:

Iteration1

Name	id	course1	course3
Kim	2344	12	34

Iteration2

Name	id	course2	course3
Mia	3445	23	44

Iteration3

Name	id	course1
Joe	3222	35

After each iteration i need to append the output to a final output structure as below

Final output

Name	id	course1	course2	course3
Kim	2344	12	0	34
Mia	3445	0	23	44
Joe	3222	35	0	0

[alexey_nikityuk]

Here you go :) Let me know if that helpe

[GaRaGe]

thanks but my output is from a batch macro. at each iteration i get one output and the next iteration gives me a different set of columns. if i use transpose like how you did, then i need to dynamically change the key fields in transpose. is there a way to do that?

[LordNeilLord · Accepted solution]

Hey @GaRaGe

In your batch macro interface designer settings choose this option:

Then add a data cleanse after to replace any nulls with 0

[GaRaGe]

thanks a bunch. didnt know this configuration. works now. 

[alexey_nikityuk]

No you would not need to change anything manually, coz it will be done dynamically. That is the beauty of the transpose/crosstab solution