Reply
Green Belt
Posts: 62
Registered: ‎08-31-2016
Accepted Solution

Giving a value to a null field

I have JIRA data coming into Domo and "Assignee_Name" is included and filterable in my chart. However things that are unassigned in JIRA come into DOMO as blank/NULL, so when you drill down by assignee and say a pie chart shows all assignee's, if you click on the piece showing no name instead of showing all of that data it just shows nothing. 

 

So i'm trying to give the null values the label "unassigned" which I was able to do with the below formula however then it put all other names in it's own category leaving the 2 options of "Unassasigned" and " " 

 

 

domo 2.png

CASE
WHEN  `assignee_name` IS NULL
THEN 'Unassigned'
END

 

 

TLDR: I get assignee names and blank values. I was to get assignee names and turn the "blank values" into string "Unassigned"


Accepted Solutions
Solution
Accepted by topic author guitarhero23
‎01-11-2017 02:27 PM
AS Major Blue Belt
Major Blue Belt
Posts: 394
Registered: ‎09-08-2016

Re: Giving a value to a null field

Filtering and drilling on nulls can be problematic.

 

In any event, your case statement could look like:

CASE WHEN `assignee_name` IS NULL THEN 'Unassigned' ELSE `assignee_name` END

I think you were just missing the ELSE part.

 

You could also use an IFNULL() function to simplify the code:

IFNULL(`assignee_name`,'Unassigned')

Which reads like 'If Assignee Name is null, replace the null with Unassigned.  Otherwise leave Assignee Name as-is'.  Same as above.  But this kind of process is so common they made a function specifically for it.

 

You might still have a problem drilling even if the beast mode is right, so consider doing this transform logic in a dataflow if the beast mode doesn't work.

Aaron
MajorDomo @ Merit Medical

**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post


All Replies
Solution
Accepted by topic author guitarhero23
‎01-11-2017 02:27 PM
AS Major Blue Belt
Major Blue Belt
Posts: 394
Registered: ‎09-08-2016

Re: Giving a value to a null field

Filtering and drilling on nulls can be problematic.

 

In any event, your case statement could look like:

CASE WHEN `assignee_name` IS NULL THEN 'Unassigned' ELSE `assignee_name` END

I think you were just missing the ELSE part.

 

You could also use an IFNULL() function to simplify the code:

IFNULL(`assignee_name`,'Unassigned')

Which reads like 'If Assignee Name is null, replace the null with Unassigned.  Otherwise leave Assignee Name as-is'.  Same as above.  But this kind of process is so common they made a function specifically for it.

 

You might still have a problem drilling even if the beast mode is right, so consider doing this transform logic in a dataflow if the beast mode doesn't work.

Aaron
MajorDomo @ Merit Medical

**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Highlighted
Green Belt
Posts: 62
Registered: ‎08-31-2016

Re: Giving a value to a null field

Great response, great solution. Works great. Thanks. 

Announcements
Domopalooza 2018 Call for Presenters! Do you have an amazing story about how Domo is revolutionizing the way you do business? Click here for more details! Thanks!