Vertical to Horizontal field (Cross TAB)

[Inactive User]

Hello Masters,

I need to transpose the data to horizontally  - i cannot use cross-tab or transpose or summarize to match my output. 

Kindly see attached raw data and output for your consideration.  Kindly advice how i can build a logic in Alteryx. 

Thanks - Guru

RAW DATA

OUTPUT

[phottovy · Accepted solution]

Hi @Inactive User 

Here is what I put together. I renamed a couple columns to specify Account 1 or 2 in the column name but I think it gets you close to what you are looking for.

[DawnDuong · Accepted solution]

Hi @Inactive User 

In this data structure, you actually need to create a number of outputs, and then join these outputs to create the desired output view.
- Step 1a: simple cross tab of the field [Accounts] > Grouped by 3 fields [PAY DATE], [REC. DATE], [CUSP#] > Choose aggregation method = Sum for the desired values. In this case, the desired values are columns [Calculated Amount] and [Calculated Refund]. Since you need 2 sums of values, it means you will need to have 2 cross tabs (1 for each value). 

- Step 1b: use Select Tool to rename the columns of the output from the Cross tab tool

Sub account 1

NRA

Sub Account 2

NRA

- Step 2a: simple sum to get the total payment and NRA by [PAY DATE], [REC. DATE], [CUSP#]. Only 1 sum tool is needed.

- Step 2b: use Select Tool to rename the columns of the output from the Sum tool

PAYMENT

NRA W/H

- Step 3a: use join tool to join the outputs from 2a and 2b

- Step 3b: Use Formula tool to calculate 

TOTAL NRA

REFUND

- Step 4: to add the row on top to show the month > there can be a number of different ways to do this, by Reporting Tool or other method. 

Hopefully the schematic flow above helps you get the desired outcome and is useful in your next tasks involving Alteryx.

Happy solving.

Dawn.

[Inactive User]

Thanks so much for your guidance. 

[Inactive User]

@phottovy Thanks so much for your guidance.