Magic ETL - filter with not in

cwolman
cwolman Contributor
in Magic ETL

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

Best Answer

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    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

Answers

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    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
  • cwolman
    cwolman Contributor

    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
  • ST_-Superman-_
    ST_-Superman-_ Coach

    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

Categories