Counts for 2 separate dates columns in a single month (SQL Dataflow)
Good afternoon. We had previously requested something very similar and accomplished it via Magic ETL but want to do the same thing, only this time using a MySQL dataflow instead of Magic ETL (previous thread - split & reappend a dataset). We've looked for documentation but unable to find anything; can anyone direct us to the documentation, if it does exist, or possibly explain this process using MySQL instead of Magic ETL?
If additional information is needed, please don't hesitate to ask! Thanks
Best Answer
-
@John-Peddle Ahh.. I understand now. Here's what you would want to do in SQL. Use the transforms to create 2 table, one holding all of the created dated, with an additional column called DateType and has a hard coded value of Created. The 2nd table would contain all of the resolved dates with an additional column called DateType and has a hard coded value of Resolved. You would then UNION these together in the output dataset. It would look something like this.
1. Create "created" table
SELECT `Created Date` AS TicketDate, 'Created' AS DateType
FROM `sample`
WHERE `Created Date` IS NOT NULL2. Create "resolved" table
SELECT `Resolved Date` AS TicketDate, 'Resolved' AS DateType
FROM `sample`
WHERE `Resolved Date` IS NOT NULL3. UNION together in output dataset
SELECT *
FROM `created`
UNION
SELECT *
FROM `resolved`You could add your other columns that you referenced in your sample when you are creating your two tables. Obvioulsy they need to be in the same order, etc.
The Domo card will automatically group the data for you by month when you tell it what to group by in the date filter. This should give you a nice grouped bar chart when the series is TicketType to show the number of created vs. resolved.
**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.1
Answers
-
I think what you are looking for is a recursive dataflow. Here is the KB article for doing it in SQL:
Here it is for ETL:
**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 -
Hey @MarkSnodgrass, appreciate your response and think I may have presented our issue inaccurately so I'd like to provide some sample data to see if that helps clarify the ask.
Started to work on a dataflow that breaks out the input dataset into 2 new tables, and attempted to union them to get the results needed but it's not working, though we believe we're on the right track at least.
Sample Data:
In a card, we'd like to show the percentage of issues resolved vs those created by month. So for September, we had 10 issues created (created date) and 6 isses resolved/completed, which would be 60%.
Problem is, using either of the existing date fields (Created or Resolved) on the X-Axis obviously won't work due to a number of reasons, such as
1) issues created in August & completed in September won't appear if using CREATED DATE
2) issues created in September but not completed until October will show up if using CREATED DATE
3) issues resolved in September would not include those created in prior months if using RESOLVED DATE
Does that make sense? From what we've read it sounds like we'd need to create 2 new tables from the inoput dataset, but need to better understand where we're going wrong because what we have so far is not correct.
Any thoughts?
0 -
@John-Peddle Ahh.. I understand now. Here's what you would want to do in SQL. Use the transforms to create 2 table, one holding all of the created dated, with an additional column called DateType and has a hard coded value of Created. The 2nd table would contain all of the resolved dates with an additional column called DateType and has a hard coded value of Resolved. You would then UNION these together in the output dataset. It would look something like this.
1. Create "created" table
SELECT `Created Date` AS TicketDate, 'Created' AS DateType
FROM `sample`
WHERE `Created Date` IS NOT NULL2. Create "resolved" table
SELECT `Resolved Date` AS TicketDate, 'Resolved' AS DateType
FROM `sample`
WHERE `Resolved Date` IS NOT NULL3. UNION together in output dataset
SELECT *
FROM `created`
UNION
SELECT *
FROM `resolved`You could add your other columns that you referenced in your sample when you are creating your two tables. Obvioulsy they need to be in the same order, etc.
The Domo card will automatically group the data for you by month when you tell it what to group by in the date filter. This should give you a nice grouped bar chart when the series is TicketType to show the number of created vs. resolved.
**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.1 -
@MarkSnodgrass - thanks so much, your solution worked exactly as we'd hoped but...
As is the case with most BI requests we receive, the requirements changed a bit and after some work we were able to work with what you gave us and made the necessary adjustments in the dataflow, and used a Line + Bar Chart to show both totals (created & resolved) as well as the percentage between the two.
Thanks so much for all your help on this request, it's greatly appreciated!
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
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 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
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 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
- 109 Community Announcements
- 4.8K Archive