cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

SOLVED

## Data Prep & Transpose

Highlighted
Asteroid

Hello All,

I have a file where each customer has different dates. There are other columns having Aging status with amount.

We are trying to represent for each customer the date of disconnect status in transpose/crosstab, and the os amount for each disconnect date has to be taken from the aging column. If the number of days are 219, the aging amount for the disconnect date will be from 180-365 days column.

 Cust Port Date1 Status DateCutOff Days 30-60 days 60-90 days 90-120 days 120-180 days 180-365 days Above 365 days 1148 TG 23-Feb-17 Disc 30-09-2017 219 8689.01 3548.95 4306.75 21395.46 2574.47 0 1148 TG 26-Jul-17 Disc 30-09-2017 66 8689.01 3548.95 4306.75 21395.46 2574.47 0 1148 TG 03-Aug-17 Disc 30-09-2017 58 8689.01 3548.95 4306.75 21395.46 2574.47 0 1148 TG 01-Sep-17 Disc 30-09-2017 29 8689.01 3548.95 4306.75 21395.46 2574.47 0 67600 TG 21-Jun-17 Disc 30-09-2017 101 972.98 1863.95 3241.61 3104.24 1050.87 1.97 67600 TG 08-Jul-17 Disc 30-09-2017 84 972.98 1863.95 3241.61 3104.24 1050.87 1.97 67787 TA 02-Aug-17 Disc 30-09-2017 59 400 890 767 267 456 312

Output

 Cust Disc1 Os/ amount Disc2 Os/ amount Disc3 Os/ amount Disc4 Os/ amount Disc5 Os/ amount Disc6 Os/ amount 1148 23-Feb-17 2574.47 26-Jul-17 3548.95 03-Aug-17 8689.01 01-Sep-17 8689.01 67600 21-Jun-17 3241.61 08-Jul-17 1863.95 67787 02-Aug-17 400

Bolide
First calculate the Os/amount in a separate column for each customer and date. Consolidate all your dates and amounts it's into a single column (values column). Then define the headers for the values column in another column (each Os/amount column will need a distinct header. Try 'Os/amount disc 1' or something). Then cross tab the headers and date and Os/amount grouped by the customer number.

Let me know if that helps!

Sorry there's not example attached. I'm not by a computer
Asteroid

Hi @Josh

Thank you so much for your reply. I was able to separate the dates for each customer, but the other steps are little hard to follow. If possible, Could you please post an example or workflow.

 Cust Date_1 Date_2 Date_3 Date_4 Date_5 Date_6 Date_7 Date_8 1148 28-01-2017 1148 04-02-2017 09-06-2017 1148 07-05-2017 08-06-2017 08-07-2017

Asteroid

Thank you very very very much for you guidance. I read through your logic again and again, and was able to reach to the desired output. Thank you so much for pointing me in the right direction.

Best Regards,

Scheruku

Labels