Hello! I am having trouble adding a new column, "Dummy", to my output dataset. Although the query is valid, the output does not produce the "Dummy" column when ran. I am trying to identify rows to remove based on the criteria defined in the LEFT JOIN. See below.
SELECT
A.`Posting Title`,
A.`Weekly Reporting Date`,
A.`Weekly Report Stage`,
A.`Funnel Stage`,
A.`Candidate Link`,
A.`Origin`,
A.`Department`,
A.`Days in Stage`,
A.`Days to Hire`,
A.`Days to Archive`,
A.`Offer Acceptance Date`,
A.`Candidate Journey Start Date`,
A.`Event Date`,
A.`Previous Event Date`,
A.`Previous Opportunity Stage`,
A.`Candidate Name`,
A.`Hiring Manager`,
A.`Posting Created Date`,
A.`Owner`,
A.`Posting Country`,
A.`Posting Location`,
A.`Team`,
A.`Posting Status`,
A.`Archive Reason`,
A.`Candidate Email`,
Bad.`Dummy`
FROM `lever_weekly_khtest` AS A
LEFT JOIN (SELECT `Candidate Name`,
"Remove" AS Dummy
FROM `lever_inactive_report`
WHERE `Candidate Name` IS NOT NULL) AS Bad
ON A.`Candidate Name` = Bad.`Candidate Name`
WHERE Bad.`Dummy` IS NULL;
Thank you!
Tagged:
FROM `lever_inactive_report`
WHERE `Archive Reason` IS NOT NULL) AS Bad
ON A.`Opportunity ID` = Bad.`Opportunity ID`;
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
2
Try replacing
"Remove"
with
'Remove'
as the double quotes can indicate a column name and not a specific value
What specifically is it returning? Is it just dropping Dummy from your dataset?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
1
It is returning every column except for the Dummy column! The single ticks did not work sadly.
Even weirder, The logic built out in the LEFT JOIN is applying to some candidates but not all, even though their names appear on the lever_inactive_report table. Any thoughts??
I'm not sure how your query is returning any results because you are hard-coding a value of Remove in your Bad table, which will make every row in the bad table a have a value. You are then wanting records from the bad table where Dummy is null, but you just populated the value of Remove in every row in that table.
I would suggest not using the dummy column and then having your final where clause be where Bad.CandidateName IS NULL to get the missing rows.
**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
My apologies, since you are doing a left join you would get results, so ignore my first comment. Are you saying you don't see the column heading? Or you don't see any values?
I have seen where the preview window doesn't always refresh when you add another column. Does it show up when you save and run the ETL and you view the dataset in the data center?
**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
No worries! The Dummy column does not show up when I run the ETL and view the dataset output.
When I am transforming and previewing the output when editing my dataflow with MySQL, the column is there.
Updated Query (also not working properly):
SELECT
A.`Posting Title`,
A.`Weekly Reporting Date`,
A.`Weekly Report Stage`,
A.`Funnel Stage`,
A.`Candidate Link`,
A.`Origin`,
A.`Department`,
A.`Days in Stage`,
A.`Days to Hire`,
A.`Days to Archive`,
A.`Offer Acceptance Date`,
A.`Candidate Journey Start Date`,
A.`Event Date`,
A.`Previous Event Date`,
A.`Previous Opportunity Stage`,
A.`Candidate Name`,
A.`Hiring Manager`,
A.`Posting Created Date`,
A.`Owner`,
A.`Posting Country`,
A.`Posting Location`,
A.`Team`,
A.`Posting Status`,
A.`Archive Reason`,
A.`Candidate Email`,
Bad.`Dummy`
FROM `lever_weekly_khtest` AS A
LEFT JOIN (SELECT `Opportunity ID`,
"Remove" AS Dummy
FROM `lever_inactive_report`
WHERE `Archive Reason` IS NOT NULL) AS Bad
ON A.`Opportunity ID` = Bad.`Opportunity ID`;
If the above select statement is what is in your output dataset, I might try moving that statement to be a new transform in your ETL and then just do SELECT * FROM last_transform_name (whatever you name the new transform) in your output dataset. Perhaps that will shake it loose.
I tend to not put much logic in the output dataset, but have it be very basic and have the transforms do any joining 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.
1
I updated my output dataset query to make it simple! Now I have the Dummy variable pulled in, but the logic built out in the LEFT JOIN is applying to some rows, but not all in the final output dataset. Any ideas?
Hi
@keeeirs10
are you saying that the
Dummy
column is only populating "Remove" in some rows, but not all? That's likely because your updated query (the second one you posted) removed the "WHERE
Dummy
is NULL". If you only want to output rows that are included in
lever_inactive_report
then I would change your join from a LEFT JOIN to an INNER JOIN.
FROM `lever_inactive_report`
WHERE `Archive Reason` IS NOT NULL) AS Bad
ON A.`Opportunity ID` = Bad.`Opportunity ID`;
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
2
