Alteryx Desginer - Split and Join

[STAR1BANU]

Dear Experts,

In Table Data A  - I have data for 12 months from Jan to Dec it has 3 lines with five different categories of the amount -

Kindly help me to split and join like in the tab - solution needed tab 

I want each month - 5 types of the amount should become next to next  like a spreadsheet 

Please share the workflow for this solution at the earliest.

[Emil_Kos · Accepted solution]

Hi @STAR1BANU,

Everything matches for now besides column names. The Alteryx don't allow columns to have the same names.

In order to change it you would need to add extra spaces to the right input file of the dynamic rename tool

This is the place that I am referring too:

I am sorry but I don't have time to do that now but even without it I think the solution is quite decent:

[Qiu · Accepted solution]

@STAR1BANU 

Make Columns tool can just do the work.
And note that Alteryx does not allow duplicated Field Names.

[danilang · Accepted solution]

Hi @STAR1BANU 

If you are sending the output back to excel, there's a option to skip field names.  Using that you can build a table that displays all the data, including the duplicated "column names" as data rows

After the Make Columns does it's job, use a field info to get the field names as a list of records.  Add a RecordId to use as a final column header.  The formula tool strips out the Column_x_ from the original field names, then the cross tab gives you a dummy header row.  Union this with the data, ensuring that you connect the dummy header row to the union tool first.  This will use the column name from the dummy row overriding the ones output from the Make Columns tool.  Your results will look like this

Just remember when you output to excel that you skip field names so the row with Month, Amount1, etc ends up as row one in spreadsheet

Dan