Union the data based on the field name

[Kaustubh17]

Hi team,

I have data in the Alteryx in the below format of the headers.

Category

Unit

Plant1

Apr-23

May-23

Jun-23

Plant2

Apr-23

May-23

Jun-23

 and so on till Plant14

I want the union the data based on Plant 1 , Plant 2 and So on.

Below is the result format.

Category	Unit	Plant1	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	FY 24
A	X	ABC	Any value
B	Y	ABC
C	Z	ABC
Category	Unit	Plant2	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	FY 24

And so on till Plant 14.

I have approximate 14 Plants so i want the union of each plant below each other.. Please let me know if you have any questions.

Best Regards,

Kaustubh

[KGT]

Easiest way is to bring the data in with headers in the first row (option in the input tool) and then 14 select tools running parallel followed by union and dynamic rename (could be Dynamic renames, then union depending on data consistentency)

The better way I can visualise this is by tagging the headers.

1. Sample the first row, just so there's less data and tag the headers using transpose and formula
2. Transpose the dataset and clean it up and then a multi-row to label the plant.
3. Join the new headers back on to the cleansed/transposed dataset
4. Filter and crosstab (filter isn't needed, just used to clean up data for the concatenate.

There are many other ways including Dynamic Selects and macros, but I think Transpose and cross-tab are your friends here.

[apathetichell]

This isn't really what Alteryx is designed for. You could output each plant on a seperate sheet dynamically - or you could use table/layout/union/summarize/render to put them horizontally on the same excel.

If I had to do what you are looking at though - the two ways would be create a column for plant id. create a new header row. append your header row. dynamically take fieldnames form header row. convert types as needed since everything is now a string. drop uneeded column.

way 2- something with a batch macro/union where I run for each plantid group and change the header value dynamically.

[Kaustubh17]

Hi @KGT 

I applied the below steps but the output is not desired thanks for you valuable input.  the result which i am getting which is random rows. but i am looking for the result, which just move the each plant segregated data below each out the format, alignment should be same. Please let me know if you have any other way. I am sharing the result format below.

Category	Unit	Plant1	Apr	May	Jun	Jul	Aug	Sep	FY
A	X	a
B	Y	a
Category	Unit	Plant2	Apr	May	Jun	Jul	Aug	Sep	FY
A	X	b
B	Y	b

and so on i have over all 14 Plant Same category same Unit, but values are different.

Please let me know if you have any question.

Best Regards,

Kaustubh

[apathetichell]

post some sample data with the sample schema.

[KGT]

I'm not sure what you're after... the solution I posted on the last post takes the data from:

And transforms it to this:

You can always put other steps in the middle or at the end to rename the fields if needed. If it's different to that, then I would need to see something clearer about what the issue is.

[Kaustubh17]

HI @KGT ,

The below result is in correct format, did you use the same workflow to create the result. Or you create another flow? Because the result which you shared is correct.

Please let me know the process behind that

Best Regards,

Kaustubh

[Pang_Hee_Choy]

@Kaustubh17  can you confirm the input format and output format?

i think we confuse on what is the input and output. 

here my try,

with input:

and output:

workflow:

macro:

[Kaustubh17]

Hi @apathetichell 

Please find the Attached excel with input sheet and output sheet.

Please let me know if you require any other things from my end.

Best Regards,

Kaustubh

[Kaustubh17]

Hi @Pang_Hee_Choy 

Please find the attached excel with input format and output format.

Please let me know if you need any other thing from my end.

Best Regards,

Kaustubh