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.
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.
("test.csv" is just an input based on the input table in your post that i used to test the workflow)
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?