ETL filter with two criteria on same field - fail

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.

Find more posts tagged with

Comments

BlueRooster

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

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

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.

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

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

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.

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'.

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