How to make NULL values display on a Table?

estelle531 · May 2021

Essentially what I'm trying to do is....

SELECT DISTINCT Loan_Type FROM Dataset within a beastmode.

I'm an having an issue where if no data exists for Type B then the entire row of the table disappears verses the type name being visible and 0s displaying.

How can I resolve?

What I want:

Type Count

Type A 5

Type B 0

Type C 1

What's happening:

Type Count

Type A 5

Type C 1

MarkSnodgrass · May 2021

You can create a beast mode that would turn a null to 0 with this:

IFNULL('Count',0)

jaeW_at_Onyx · May 2021

@estelle531 ,

@MarkSnodgrass is correct, you can fill NULLs with 0 ; however, that only works if there is a row in the dataset for Type B on that date (or whatever the rest of your criteria are).

Domo cannot generate data for an axis if the data doesn't exist.

If Type B doesn't exist in your data, there's nothing to fill. Confirm that the data exists and if it doesn't create a placeholder row.

estelle531 · May 2021

@jaeW_at_Onyx How can I go about creating a placeholder row?

MarkSnodgrass · May 2021

@estelle531 You can create the placeholder row by using Magic ETL. You would need a dataset that has a list of your different types and then do a left join to your main dataset and join on the type column. This will insert any missing types and have a null value for your count. You can then use the IFNULL function to replace it with 0.

jaeW_at_Onyx · May 2021

@estelle531 you would need to build the universe of template rows at the granularity you want to be able to filter / pivot your data on.

in this video I describe it as "building the universe" of options

https://youtu.be/Xb4QgKYgaqg?t=168

estelle531 · May 2021

@jaeW_at_Onyx and @MarkSnodgrass

Thank you both so much for the help. I'm gonna try to implement the suggestions you gave and I will let you know how it goes.

How to make NULL values display on a Table?

Answers

Categories