Applying Filter Tile to Remove Nulls Not Working

Options
pauljames
pauljames Contributor
in Magic ETL

Any ideas as to why this isn't happening?

IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

Tagged:

Answers

  • DomoDork
    DomoDork Contributor
    Options

    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
    pauljames Contributor
    Options

    @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?

    IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

  • MichelleH
    MichelleH Coach
    Options

    @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
    Godiepi Coach
    Options

    @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

    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • pauljames
    pauljames Contributor
    Options

    @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?

    image.png

    IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

  • MichelleH
    MichelleH Coach
    Options

    @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
    DomoDork Contributor
    Options

    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
    pauljames Contributor
    Options

    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!

    IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

Categories