Split dates in single column into multiple columns

DrGWright

I want to take a column that contains dates and split it into at least 2 columns to find the date difference between the two.  So, for example, I have a client ID and a transaction ID that are unique.  The transaction ID does not repeat, but the client ID does based on their purchases.  My date column is their purchase date.  I want to see how long it has been since the last time they ordered something.  I know I'll need to do a date diff in the ETL once I get the column split, but I cannot figure out how to split the date column to show the "earliest purchase date" and then the "next purchase date."

DataMaven

Can you provide an example?  Perhaps the info below will help...

 

The initial question, it seemed, was to see how long it has been since they last ordered.  Steps for that would be:

1 - Group By - You'll use this function to create a summary (Likely just 'Client ID' in the top section).  In the bottom section, you will include your purchase date, and select the "Maximum" option to get  the latest date. 

2 - Add Constants - Make the value 'Current Date'. 

3 - Date Operations - Difference between 'Current Date' and the last order date.   

4 - Join - Loop the results back into your dataflow, joining on Client ID. 

 

You now have 2 additional columns - 'Last Order Date' and 'Time Since Last Order' (or whatever you choose to name them).  Just make sure not to sum 'Time Since Last Order'.  

 

This can be used to add a lot of other information, like last order flagging, and even time between orders, if you add some rank and window functions.