Split dates in single column into multiple columns

DrGWright
DrGWright Member
in Magic ETL

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

Best Answer

  • DataMaven
    DataMaven Coach
    Answer ✓

    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.  

     

     

     

     

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • DataMaven
    DataMaven Coach
    Answer ✓

    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.  

     

     

     

     

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • DrGWright
    DrGWright Member

    That worked BEAUTIFULLY!!!!  Thanks so much! @DataMaven 

Categories