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

Find more posts tagged with

Workbench

Magic ETL

Accepted answers

BlueRooster

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

All comments

BlueRooster

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

nitot

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

BlueRooster

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.

nitot

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

Thanks again

BlueRooster

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.

nitot

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

BlueRooster

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

nitot

This looks like it will work, thanks!