Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Transpose identical field columns

jamiep004
7 - Meteor

Hi, below is an example of information that I have in a excel sheet:

 

 

Name    |  Date     |  CC Statement | CC Repayment |  Balance |  Date     |CC Statement |  CC Repayment  |   Balance   |   

John      | Jun-18  |        600            |         -600          |        0      |  Jul-18   |      320           |               -             |      320       |     

Mary      | Jun-18  |        950            |         -400          |     550     |   Jul-18  |                       |                              |     550        |            

 

 

I would like this information transposed in a way (as shown below for example) that allows me to create a pivot table in excel:

 

Name   |   Date    |     Category        |     Value    |

John     |  Jun-18 |   CC Statement  |     600       |

John     |  Jun-18 |  CC Repayment |     -600      |

John     |  Jul-18  |   CC Statement  |     320       | 

Mary     |  Jun-18 |  CC Statement   |      950      |

Mary     |  Jun-18 |  CC Repayment |     -400      |

   

 

The columns CC Statement and CC Repayment repeat for each month of the year going across. In my actual worksheet I have more 'categories' but for the purpose of ease I limited the example to 2 columns. When trying to transpose the information above Alteryx renames duplicates (CC Statement, CC Statement 2, etc). I accomplished my goal but placing the data for each month in separate sheets. But I would like to know if I can get the result I want using one sheet as above. 

 

Thanks.

7 REPLIES 7
adamorse
9 - Comet

I've attached a workflow that might give you some ideas. The basic outline is that you first transpose *everything*. Then you can extract the digit that the transpose adds (2,3,...) and use that as an index to join the data together into a single table. As you can see in the workflow output below, the "Right_Index" is calculated by pulling the digit the transpose adds and is used to associate the correct dates and payments together.

 

identical field columns.PNG

 

("test.csv" is just an input based on the input table in your post that i used to test the workflow)

jamiep004
7 - Meteor

Thanks a million! It worked.

jamiep004
7 - Meteor

Hi there, I need further help on the workflow provided. The transpose tool is adds single digits up to 9. Then any repeat of identical columns after that gives 9_2. The right index is only pulling the 2 whereas I will need the entire 9_2 extracted. Is this possible?

 

 

Thanks.

adamorse
9 - Comet

Maybe try replacing the formula that creates the "Index" variable (first in the Formula tool) with something like

 

If Contains([Name], "_") Then Right([Name],Length([Name])-FindString([Name], "_")+1) Else Right([Name], 1) EndIf

 

Let me know if you need any other pointers on this

jamiep004
7 - Meteor

Thanks for your reply. There are three expressions in the formula tool. I'm not sure which one I should replace. Can you guide further please?

jamiep004
7 - Meteor

It have figured it out. Thanks for your help!

asteryx
8 - Asteroid

@adamorseFound this while trying to solve a similar problem. Thanks for your input. I had to do a lot of adjustments, but your approach was key.

Labels