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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

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

 

 

CustPortDate1StatusDateCutOffDays30-60 days60-90 days90-120 days120-180 days180-365 daysAbove 365 days
1148TG23-Feb-17Disc30-09-20172198689.013548.954306.7521395.462574.470
1148TG26-Jul-17Disc30-09-2017668689.013548.954306.7521395.462574.470
1148TG03-Aug-17Disc30-09-2017588689.013548.954306.7521395.462574.470
1148TG01-Sep-17Disc30-09-2017298689.013548.954306.7521395.462574.470
67600TG21-Jun-17Disc30-09-2017101972.981863.953241.613104.241050.871.97
67600TG08-Jul-17Disc30-09-201784972.981863.953241.613104.241050.871.97
67787TA02-Aug-17Disc30-09-201759400890767267456312

 

Output

 

CustDisc1Os/ amountDisc2Os/ amountDisc3Os/ amountDisc4Os/ amountDisc5Os/ amountDisc6Os/ amount
114823-Feb-172574.4726-Jul-173548.9503-Aug-178689.0101-Sep-178689.01    
6760021-Jun-173241.6108-Jul-171863.95        
6778702-Aug-17400          

 

Attached the sample data. Please help. 

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. 

 

CustDate_1Date_2Date_3Date_4Date_5Date_6Date_7Date_8
114828-01-2017       
114804-02-201709-06-2017      
114807-05-201708-06-201708-07-2017     

 

DiscDates.PNG

Asteroid

Hi @JoshKushner

 

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