ETL filter with two criteria on same field - fail

Sweep_The_Leg
Sweep_The_Leg
in Archive

I have a dataset that i'm trying to filter on a text field in magic ETL. I set the filter for Contains, Specific Vaule, ABC. It runs just fine. But then I add a second filter rule on the same column for DEF, expecting a result of all rows with ABC or DEF. as soon as I add any second rule on the same column, the result is zero rows. No errors, but no results. 

 

 

 

Comments

  • Valiant
    Valiant Coach

    I may be wrong, but I'm pretty sure this functions as an AND clause. So you're asking if any rows contain ABC AND DEF. Are there records with those in the dataset?

  • Sweep_The_Leg
    Sweep_The_Leg

    And i'm probably needing an OR clause. Some fields are ABC, others are DEF. 

     

    Obvious workaround is creating two filters, one for each, but I was hoping to get around that. In my real dataset, I have 18 individual instances, that would group into 5 categories. I just didn't want to have to make 18 filters. 

  • ST_-Superman-_
    ST_-Superman-_ Coach

    I would suggest venturing down the MySQL or Redshift dataflow options then.  ETL is a great tool for quick dataflows, but it can get pretty intense the more you try to do with it.


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

    There's something else going on here. when I trim down my input to <5K rows, the filters work fine with multiple conditions. Any one experiencing issues with datasets with 500K+ rows?

  • Sweep_The_Leg
    Sweep_The_Leg

    Ok, i just tried DOES NOT CONTAIN and used two different criteria and it worked. I switched it to CONTAINS and now i'm back to zero results. 

     

    Suggestions?

  • ST_-Superman-_
    ST_-Superman-_ Coach

    Are you getting 0 results in the preview?  ETL does strange things with large data sets when you are trying to preview.  I would reccomend making an output dataset and clicking on save/run.  Let me know if your output data set still doesn't show any results.


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

    For the filter issue, there's a drop down that's available at the top after you've entered your criteria. It defaults to 'all' but if you want results that match either ABC or DEF just switch it to 'any'. 132.JPG

     

    For the lack of results when running a preview, when running a larger dataset only the first few lines of data have the transforms applied to them so you don't get an accurate display. Just run the full ETL to get the results you want.

     

     

    Thanks

Categories