Counts for 2 separate dates columns in a single month (SQL Dataflow)

Reply
Highlighted
Green Belt

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


Accepted Solutions
Major Blue Belt

Re: Counts for 2 separate dates columns in a single month (SQL Dataflow)

@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 NULL

2. Create "resolved" table

SELECT `Resolved Date` AS TicketDate, 'Resolved' AS DateType
FROM `sample`
WHERE `Resolved Date` IS NOT NULL

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




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post


All Replies
Major Blue Belt

Re: Counts for 2 separate dates columns in a single month (SQL Dataflow)

@John-Peddle 

I think what you are looking for is a recursive dataflow. Here is the KB article for doing it in SQL:

https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Creating_a_Recursive%2F%2FSnapshot_SQL_D...

 

Here it is for ETL:

https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Creating_a_Recursive%2F%2FSnapshot_ETL_D...

 

 




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Green Belt

Re: Counts for 2 separate dates columns in a single month (SQL Dataflow)

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:Screen Shot 2019-10-09 at 6.45.43 PM.png

 

 

 

 

 

 

 

 

 

 

 

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? 

Major Blue Belt

Re: Counts for 2 separate dates columns in a single month (SQL Dataflow)

@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 NULL

2. Create "resolved" table

SELECT `Resolved Date` AS TicketDate, 'Resolved' AS DateType
FROM `sample`
WHERE `Resolved Date` IS NOT NULL

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




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post

Green Belt

Re: Counts for 2 separate dates columns in a single month (SQL Dataflow)

@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! Smiley Happy

Announcements
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!