community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Transpose identical field columns

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.

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)

Meteor

Thanks a million! It worked.

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.

Highlighted
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

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?

Meteor

It have figured it out. Thanks for your help!

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