Vertical to Horizontal field (Cross TAB)

Question

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

NQI.xlsx

phottovy · Accepted Answer

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 Answer

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 1NRASub Account 2NRA

- 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

PAYMENTNRA W/H

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

- Step 3b: Use Formula tool to calculate

TOTAL NRAREFUND

- 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.