How to make NULL values display on a Table?

estelle531
estelle531 Member
edited May 2021 in APIs & Domo Developer

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

Answers

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

    IFNULL('Count',0)

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • @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.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • estelle531
    estelle531 Member
    edited May 2021

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

  • @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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • @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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • @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.