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

John-Peddle

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

MarkSnodgrass

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

MarkSnodgrass

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

 

Here it is for ETL:

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

 

 

John-Peddle

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? 

MarkSnodgrass

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

John-Peddle

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