Magic ETL Exclusion
I'm looking for an efficient way to only keep contacts that appeared in the year 2018 and exclude contacts that appear in the year 2019 AND 2018. It's possible for a contact to appear several times in the same year, so I cannot simply use the Remove Duplicates tile. In Magic ETL, how would I do the equivalent of:
SELECT email_addr, year FROM Dataset1
where year = '2018' and email_addr not in
(
SELECT email_addr FROM Dataset1
where year = '2019'
);
My current method is using 1 Input DataSet, then using 2 filters. One for 2018 and the other for 2019. Then, I am using Join Data to do Left Outer. Next, I'm using a Filter to exclude any rows where the Year is null for the Datatset with a 2019 filter. It works, but is there a more efficient way to do this?
Best Answer
-
You did that correctly as it looks like you are wanting to get contacts that only visited in 2018 and exclude contacts that visited in 2018 and then again in 2019.
**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
-
Correct me if I'm wrong, but you just want to have 2018 contacts?
Why not just filter year 2018?
0 -
You did that correctly as it looks like you are wanting to get contacts that only visited in 2018 and exclude contacts that visited in 2018 and then again in 2019.
**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 just want contacts who are 12M+ "inactive". In my dataset, it's possible 2018 contacts could also be in 2019 and these would be "active" (recent) contacts.
0 -
You could also do a calculation to get the max date for each contact ID. Then perform a calculation to determine how many days ago that was. Finally filter out any contacts where there have been more than 365 days since the last activity.
This way you wouldn't be restricted to a calendar year (and having to rework it each year) but would always just have a list of contacts that have activity in the last 365 days.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 57 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive