Milestone tracking data model

Question

Hello,

I have some challenges manipulating milestones data in Alteryx and hoping someone can help.

Below is the input data that includes milestone start and end dates for 2 items. My goals is to calculate # of days it takes to complete each milestone for each item as well as to add a flag indicating whether the milestone is required or not (i.e. if there is a start date, then milestone is required, otherwise it is not). Some milestones may have a start date but no completed date so the days_to_complete will be 0, however I still want to show it as a required milestone.

I've tried to slice and dice the data different ways in Alteryx but wasn't able to get the desired output. Appreciate if anyone can point me in the right direction.

Current Input

Item #Milestone_CategoryMilestonePerformedOn1Market AnalysisMarket Analysis_started1/1/201Market AnalysisMarket Analysis_completed1/15/201Rough PrototypesRough Prototypes_started2/6/201Rough PrototypesRough Prototypes_completed2/26/201Cost EstimatesCost Estimates_started1/20/201Cost EstimatesCost Estimates_completed3/1/201ArchitecturesArchitectures Design_started2/1/201ArchitecturesArchitectures Design_completed2/15/201Refine DesignRefine Design_started2/23/201Refine DesignRefine Design_completed 2Market AnalysisMarket Analysis_started1/1/202Market AnalysisMarket Analysis_completed 2Rough PrototypesRough Prototypes_started2/6/202Rough PrototypesRough Prototypes_completed 2Cost EstimatesCost Estimates_started 2Cost EstimatesCost Estimates_completed 2ArchitecturesArchitectures Design_started 2ArchitecturesArchitectures Design_completed 2Refine DesignRefine Design_started 2Refine DesignRefine Design_completed

Desired Output

Item#Milestone_Category#days_completemilestone_required1Market Analysis1111Rough Prototypes1511Cost Estimates3011Architectures Design1011Refine Design 12Market Analysis 12Rough Prototypes 12Cost Estimates 02Architectures Design 02Refine Design 0

MichalM · Accepted Answer

@jannet2014

* Firstly, I'd convert the dates into Alteryx friendly format (yyyy-mm-dd) using the DateTime module
* I'd then re-code the Milestone field and remove the reference to category leaving me with only Started/Completed flag
* Once the data's clean, I'd use the Crosstab module and reshape it to get Started and Completed dates on the same line
* Lastly I'd create two new variables - milestone_required and #days_complete

See the attached example