Alteryx Desginer - Split and Join

Question

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.

Sample Data 2.xlsx

Emil_Kos · Accepted Answer

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 Answer

@STAR1BANU

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

danilang · Accepted Answer

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