NULL value Handling

Options
ARosser
ARosser Member

I'm a bit frustrated by this one. I have done everything along the path to ensure NULL values are being handled appropriately in my data. Starting from flat files and ending in a dataset (with several non-Domo steps in the middle). All of my data appears as expected in the dataset, but as soon as I add the column to a card the NULLs turn into a blank and I lose the ability to filter on them. From googling it appears that this is a common issue with the suggested "fix" being to use a case statement to "create" a value. I'm dealing with a dataset that is 237 columns wide. That is a ridiculous task to create BeastModes for at least 2/3rds of those. Please tell me there is another option. Please!

Tagged:

Best Answer

  • DavidChurchman
    Answer ✓
    Options

    Yeah, the null handling ain't great. If you're using an ETL and have access to the R/Python tiles, you could handle all your nulls in all your columns with a quick script:

    library(dplyr)
    library(tidyr)
    library('domomagic')
    
    input1 <- read.dataframe('Alter Columns 2')
    
    input1 <- input1 %>% 
      mutate(
        across(everything(), ~replace_na(.x, 'Something to replace your NAs with'))
      )
    
    
    write.dataframe(input1)
    
    
    

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • DavidChurchman
    Answer ✓
    Options

    Yeah, the null handling ain't great. If you're using an ETL and have access to the R/Python tiles, you could handle all your nulls in all your columns with a quick script:

    library(dplyr)
    library(tidyr)
    library('domomagic')
    
    input1 <- read.dataframe('Alter Columns 2')
    
    input1 <- input1 %>% 
      mutate(
        across(everything(), ~replace_na(.x, 'Something to replace your NAs with'))
      )
    
    
    write.dataframe(input1)
    
    
    

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • MichelleH
    Options

    @ARosser Can you walk us through what other steps you have taken to regarding null handling?

  • ARosser
    ARosser Member
    Options

    Thanks @DavidChurchman. I will look into this further.

    @MichelleH nothing crazy. Just making sure Visual Studio, SQL, and Domo Workbench are consistently treating empty strings as NULLs. We don't have control over the source data formatting so I have to clean everything up on my end. But it all goes wonky once it hits a Domo card because NULL just doesn't exist.