Surveys in specific Date Range
Good morning,
I currently have a dataset being created from our Qualtrics connector that counts the number of surveys and averages scores from specifi questions in a given time period. The time period we go off of would be between the Last Day of the Quarter (Period End) and 180 days before the Last Day of the Quarter (Period Begin.) Below is my code I am using in the ETL process:
SELECT
`Agent` ,
U.`Position`,
U.`BranchDescription`,
(ADDDATE(`LastDayOfQuarter`, -180)) AS MinDate,
(`LastDayOfQuarter`) AS MaxDate,
COUNT(DISTINCT `Response ID`) as SurveyCount,
AVG(CASE WHEN `Question ID` = 'Q1_2' THEN `Answer Value` END) AS EngagedScore,
AVG(CASE WHEN `Question ID` = 'Q1_3' THEN `Answer Value` END) AS KnowledgableScore,
AVG(CASE WHEN `Question ID` = 'Q1_6' THEN `Answer Value` END) AS ValuedScore
FROM `qualtrics_responses`
LEFT JOIN `date` D
ON (D.`Date`) = (DATE(`End Date`))
LEFT JOIN `users` U
ON U.`UserID` = `Agent ID`
WHERE `Agent` IS NOT NULL
AND DATE(`End Date`) <= (`LastDayOfQuarter`)
AND DATE(`End Date`) >= (ADDDATE(`LastDayOfQuarter`, -180))
GROUP BY `Agent`, (ADDDATE(`LastDayOfQuarter`, -180))
I have run into an issue where if the Agent does not have any surveys this year, my query is not creating the results for the current range of 10/03/2019 - 03/31/2020. I have tried a few different ways starting with my Date table but cannot seem to figure out a way that works. I know the issue stems from the way I am joining to my date table, but again, I have hit a wall and am hoping to find some answers here. Thank you in advance!
Comments
-
The first thing that jumps out to me is that you need to change your table listing in your FROM clause. In order to get all of the dates from the dates table, that table needs to be listed first. Your FROM and LEFT JOIN syntax should look like this:
FROM `date` D
LEFT JOIN `qualtrics_responses`
ON (D.`Date`) = (DATE(`End Date`))See if that does the trick for you.
**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 -
Unforunatley that didn't work. I beleive the main problem is that since the end date (date survey received) is being joined with date, since there have been no surveys for an agent in 2020, the most recent PeriodEnd date it is pulling is December 31, 2019.
I'm wondering if I should make some sort of CASE statement checking that the end date is between the period I am looking at, and if so to then count it?
0 -
You'll likely need to do a full outer join of the dates table and a distinct list of your agents to get the possible combinations of agent and dates. You can do this as a transform and then select that table and left join it to your qualtrics_responses table on agent and date. This should populate all possible records even if an agent doesn't have a response on a specific date (although it'll greatly increase your data set size).
Depending on how your data is structured you might be able to tweak your join instead to use a COALESCE(`End Date`, CURRENT_DATE) assuming you have records with a null end date for active records.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
@Cartergan Do you mind re-posting your SQL with a table alias for each column? I'm having trouble discerning which columns are from which table and that will make a difference when it comes to your WHERE clause, etc...
**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 -
Sorry about that! It's a bad habbit I am trying to break. Here is the updated code:
SELECT
Q.`Agent` ,
U.`Position`,
U.`BranchDescription`,
(ADDDATE(D.`LastDayOfQuarter`, -180)) AS MinDate,
(D.`LastDayOfQuarter`) AS MaxDate,
COUNT(DISTINCT Q.`Response ID`) as SurveyCount,
AVG(CASE WHEN Q.`Question ID` = 'Q1_2' THEN Q.`Answer Value` END) AS EngagedScore,
AVG(CASE WHEN Q.`Question ID` = 'Q1_3' THEN Q.`Answer Value` END) AS KnowledgableScore,
AVG(CASE WHEN Q.`Question ID` = 'Q1_6' THEN Q.`Answer Value` END) AS ValuedScore
FROM `qualtrics_responses` Q
LEFT JOIN `date` D
ON (D.`Date`) = (DATE(Q.`End Date`))
LEFT JOIN `users` U
ON U.`UserID` = Q.`Agent ID`
WHERE Q.`Agent` IS NOT NULL
AND DATE(Q.`End Date`) <= (D.`LastDayOfQuarter`)
AND DATE(Q.`End Date`) >= (ADDDATE(D.`LastDayOfQuarter`, -180))
GROUP BY Q.`Agent`, (ADDDATE(D.`LastDayOfQuarter`, -180))---
@GrantSmith I'll give that a try now and let you know if it works!
0 -
Not sure if this will work since I don't have the data to work with, but might get you closer. Basically, I made a subquery that gets all the users and their survey data if they have it. I then left join it to the date table and moved the where clause information to part of the left join criteria. I am also hoping the agent name is in the user table since you can't really pull it from the survey data if they never completed the survey.
Hope this helps.
SELECT
UQ.`Agent` ,
UQ.`Position`,
UQ.`BranchDescription`,
(ADDDATE(D.`LastDayOfQuarter`, -180)) AS MinDate,
(D.`LastDayOfQuarter`) AS MaxDate,
COUNT(DISTINCT UQ.`Response ID`) as SurveyCount,
AVG(CASE WHEN UQ.`Question ID` = 'Q1_2' THEN UQ.`Answer Value` END) AS EngagedScore,
AVG(CASE WHEN UQ.`Question ID` = 'Q1_3' THEN UQ.`Answer Value` END) AS KnowledgableScore,
AVG(CASE WHEN UQ.`Question ID` = 'Q1_6' THEN UQ.`Answer Value` END) AS ValuedScore
FROM `date` D
LEFT JOIN
(SELECT U.`Agent` , --hoping this column exists for the agent name
U.`Position`,
U.`BranchDescription`,
Q.`Response ID`,
Q.`Question ID`,
Q.`Answer Value`,
Q.`End Date`
FROM `users` U
LEFT JOIN `qualtrics_responses` Q
ON U.`UserID` = Q.`Agent ID`
) UQ
ON (D.`Date`) = (DATE(UQ.`End Date`))
AND UQ.`Agent` IS NOT NULL
AND DATE(UQ.`End Date`) <= (D.`LastDayOfQuarter`)
AND DATE(UQ.`End Date`) >= (ADDDATE(D.`LastDayOfQuarter`, -180))
GROUP BY UQ.`Agent` ,
UQ.`Position`,
UQ.`BranchDescription`,
(ADDDATE(D.`LastDayOfQuarter`, -180)),
(D.`LastDayOfQuarter`)**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.2 -
Sorry late for joining late in the game...
Question, do you know for a fact that you have EndDates for all your Data? If you don't have an EndDate for all your data, then any type of JOIN should filter it out (hence I think why you ended up putting Date on the LEFT side.
I think what I would do is create a EndDate_clean on the fact table such that:
SELECT
a.*
coalesce( end_date, date_add(now() ) as EndDate_clean //or similar
FROM
factTable aThis way when you do your LEFT JOIN on factTable any activity that doesn't have an EndDate gets lumped in with the current quarter. it also makes it visible in your output dataset, the derived version of the EndDate while retaining the actual version of the EndDate.
If you Output this as a 'final dataset', then you can use a FUSION to JOIN your transactions to your date table (on EndDate_clean) and any other relevant tables. Now you can do aggregations in cards AND it keeps your logic way more transparent to the end users.
jae.wilson@domo.com
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2
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
- 109 Community Announcements
- 4.8K Archive