How could we implement a SQL Flow that would split lines based on certain logic
Hello,
I have an ask where I need to split lines when there are multiple Users working at the same time in order to accurately count how many users are working together during a certain interval.
If I have 3 users working each one during different periods I want to split the Initial rows as per below picture. If I am able to do this then I can accurately count how many users worked during a period.
For example, I can say that between 11.30 and 11.40 I had User 1 and User 3 working together.
I was trying to do this in a SQL Flow but given the fact that we cannot use CTEs nor Cursors in MYSQL 5.6 I was wondering if someone else would have an idea on how to do this. It does not have to be done with Flows, it could be ETL or anything else as long as I get the splits in a new dataset that would work for me.
Thank you
Best Answer
-
@FXM32 Not sure if you are still needing help on this, but I put together this in Magic ETL that may help you.
Here is what is happening in these tiles:
Use the Select columns to select just the start and end time columns and then use the Remove Duplicates to get a distinct list of all start and end times.
Add a Constants tile with a field called JoinKey and a value of 1 and then do an Inner Join on that field. This will give a list of all start and end times next to each original entry that you will use in the next tile to compare against.
Use the Formula tile to check to see if they worked during that time slot.
CASE when UserStartTime >= StartTime and UserEndTime <= EndTime then 'Yes'
ELSE 'No'
ENDUse the filter tile to filter where rows equal to Yes.
Hopefully this helps.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
Answers
-
@FXM32 Not sure if you are still needing help on this, but I put together this in Magic ETL that may help you.
Here is what is happening in these tiles:
Use the Select columns to select just the start and end time columns and then use the Remove Duplicates to get a distinct list of all start and end times.
Add a Constants tile with a field called JoinKey and a value of 1 and then do an Inner Join on that field. This will give a list of all start and end times next to each original entry that you will use in the next tile to compare against.
Use the Formula tile to check to see if they worked during that time slot.
CASE when UserStartTime >= StartTime and UserEndTime <= EndTime then 'Yes'
ELSE 'No'
ENDUse the filter tile to filter where rows equal to Yes.
Hopefully this helps.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
Hi Mark,
Thank you very much for your help. Finally I have done it in MySQL code but I have followed the pattern you described and it worked for me.
Much appreciated
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive