Applying Filter Tile to Remove Nulls Not Working

pauljames

Any ideas as to why this isn't happening?

DomoDork

Hi @pauljames

I have had this happen to me twice in the past. The first time it was because the value wasnt actually a NULL but instead a blank. The second time was when I was actually trying to filter out blanks (not NULLs) and there ended up being an invisible character in my blanks that caused the filter to misinterpret things.

pauljames

@DomoDork , yes even after apply ifnull() I'm still getting an insane amount of null rows returned. The input dataset is only 5.1k rows, but then after appying ifnull() I'm getting ~200k rows returned. It's very strange. Any others out there?

MichelleH

@pauljames I agree with @DomoDork that blank values are likely the culprit. Are you using IFNULL or NULLIF? The former checks whether a value is null, the latter forces certain values to null. Try this formula for your filter to check for nulls and empty strings:

`fieldName` is not null and trim(`fieldName`) <> ''

Godiepi

@pauljames have you tried using a formula for your filter ?

something like this :

(CASE WHEN IFNULL(`YourField`,0) = 0 THEN 0

WHEN TRIM(`YourField`) = '' THEN 0

ELSE 1 END) = 1

It should exclude everything that is NULL or Blank

pauljames

@DomoDork @MichelleH @Godiepi

any reason why an input of 5.1k rows turns into an output of 318k rows? I think this is the root of the problem…

I'm just putting the file inside magicetl and creating an output…and for some reason that adds a ridiculous amount of rows…mostly new null rows I'm seeing.

any answers as to why?

MichelleH

@pauljames What do you have set as the Data Selection on the input tile and Update Method on the Output tile? Is it possible either dataset is set to Append?

DomoDork

I wonder if rows at the end of your range in excel has a bunch of blank rows. What happens if you open the xlxs file, go to the first empty row at the bottom of your data, highlight all rows below it and right click → delete rows, then re-save and re-import into Domo?

pauljames

it was the append option at the output file. Now I know that’s a thing. :)

we have a few of us working in the same etl at the moment and it was selected without realizing. Thank you all!