Filter out junk data

There is a dataset I import using Workbench, then transform using Magic ETL.  The data includes values between 1-10, but sometimes will contain a random junk value like 324568.  I'd like to filter out all values that don't fall between 1-10, but can't find a way to do this wihtout specifying every possible junk value, ie. replace 11 with Null, replace 12 with Null...this will take quite some time to reach 324568

Best Answer

Answers

  • You could do a select from your data and use the following Case statement to replace all values > 10 with NULL.

     

    SELECT CASE WHEN `numbercolumn` > 0 AND `numbercolumn` <= 10 THEN `numbercolumn` END AS 'Cleaned Number'
    FROM dataset

    When using a case statement, anything that is not specified to be captured by a the WHEN clause will be replaced with a NULL.

     

    Let me know if you have any questions on this.


    Sincerely,
    Valiant

  • Thanks!  Would this Case statement be used in a Beastmode, or can this be done on the data level?

  • Either would work. Use it in a SELECT statement to take care of it at the data level, or just the CASE statement itself as a beastmode.

  • Is the SELECT statement used in a Workbench Transform, in an ETL, or somewhere else?  

    Thanks again

  • It can be done in Workbench or within a MySQL transform. 

     

    The easiest method is probably just to create a beastmode and then check to the box to 'Save to dataset' so you can reuse it as needed.

  • I originally thought about using Beast mode, but I already have dozens of cards built and would rather fix the data than edit all of the cards to use new fields.  I can't for the life of me find how to use a Select statement in Workbench, do you know if there is a guide for this?  Can it be done with a Filter Transform or Search/Replace Transform?

    Thanks again

  • This looks like it will work, thanks!

This discussion has been closed.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!