Split dates in single column into multiple columns
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
-
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"1
Answers
-
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"1 -
That worked BEAUTIFULLY!!!! Thanks so much! @DataMaven
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 753 Beast Mode
- 61 App Studio
- 41 Variables
- 692 Automate
- 177 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive