Magic ETL - filter with not in

Reply
Major Blue Belt

Magic ETL - filter with not in

Please forgive me if this is a stupid question but until this point all of my dataflows have been in mySQL or Redshift and I am trying to utilize the Magic ETL where possible looking for performance gains.  Common scenario is to get a list of accounts and use that in a where clause to exclude those records from a result. 

Example - select * from data where account not in(select account from exclusion_accounts)

 

This needs to be a dynamic list because the values are always changing so I cannot create a filter by with each value.

Can this be accomplished in Magic ETL?

 

Thank you.


-----------------
Chris

Accepted Solutions
Black Belt

Re: Magic ETL - filter with not in

I would suggest creating a webform called "exclusion_accounts"

 

You could then bring that into your ETL as another input data set.  Then join it to your table with an outer join so that you end up with only the accounts not in the exclusion data set.

 

Let me know if you need more details


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________

All Replies
Black Belt

Re: Magic ETL - filter with not in

I would suggest creating a webform called "exclusion_accounts"

 

You could then bring that into your ETL as another input data set.  Then join it to your table with an outer join so that you end up with only the accounts not in the exclusion data set.

 

Let me know if you need more details


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Major Blue Belt

Re: Magic ETL - filter with not in

Thank you.  Thinking about the magic etl flow differently than typical sql had me turned around a bit.  My list of exclusion account numbers are available from another dataset but I was stuck on the not in syntax and did not think about outer joining and filtering the nulls.

 

Thanks again. 


-----------------
Chris
Black Belt

Re: Magic ETL - filter with not in

There is definitely a bit of a learning curve when trying to switch from MySQL to ETL data flows.  I find myself using a lot of both.  Somethings make more sense to me in ETL (particularly collapsing or uncollapsing data sets and windowed functions) while other things seem easier to do in MySQL.

 

The biggest pointer that I can offer to the ETL dataflows is to always name your tiles with something that will tell you exactly what the step is doing


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Announcements
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!