Skip to content

    • Workbench

    Workbench

    Filter out junk data

    Member
    in Workbench

    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

    Tagged:

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    Answers

    • Domo Employee

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

       

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

    • Member

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

    • Domo Employee

      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.

    • Member

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

      Thanks again

    • Domo Employee

      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.

    • Member

      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

    • Domo Employee
      Answer ✓

      You can try the following (using something like the IF value under Category:Logical)

      https://knowledge.domo.com/Connect/Connecting_to_Data_Using_Workbench_4/Creating_Columns_in_Workbench_4_Using_Calculations

    • Member

      This looks like it will work, thanks!

    This discussion has been closed.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories