5 Replies Latest reply on Jan 30, 2019 7:43 PM by paul.winiecki

Last Non-Null Value in a Hierarchy

paul.winiecki Jan 30, 2019 2:38 PM

Hello,

I have a Hierarchy that is set up as Color>Suit>Card in a Tableau 2018.3 packaged workbook. Some of the "Card" values are NULL. I would like to create a new variable where for those Card values that are NULL it would show the Suit, otherwise it would show the Color. I've tried creating calculated fields to accomplish, but I haven't been able to figure it out. Do any of you know of a way this could be done? For certain reasons, I can't have the solution to this be a Table Calculation either. Any help would be greatly appreciated. I've attached the packaged workbook in this post.

Thanks!

Paul

Last Non-Null Value in Hierarchy.twbx 42.1 KB

1. Re: Last Non-Null Value in a Hierarchy

Joe Oppelt Jan 30, 2019 2:54 PM (in response to paul.winiecki)

First of all, it looks like you have the actual data value of "Null" in your data. Not a NULL value there.

So this calc does what you want:

IF [Card] = "Null"
THEN [Suit]
ELSE [Card]
END
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
2. Re: Last Non-Null Value in a Hierarchy

Zhouyi Zhang Jan 30, 2019 2:55 PM (in response to paul.winiecki)

Hi, Paul

See my solution below. Your Null value actually is a string, you should use quote.

Hope this helps

ZZ
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
3. Re: Last Non-Null Value in a Hierarchy

Joe Oppelt Jan 30, 2019 2:56 PM (in response to Joe Oppelt)

And if you want it to continue looking to the [Color] value of Suit is also null:

IF [Card] = "Null"
THEN
If [Suit] = "Null" then [Card] ELSE [Suit] END
ELSE [Card]
END
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
4. Re: Last Non-Null Value in a Hierarchy

Joe Oppelt Jan 30, 2019 2:58 PM (in response to Joe Oppelt)

BTW: If your actual data has actual NULLs instead of the string value "Null", you can use the ISNULL() function:

IF ISNULL([Card]) ....

Or

IF NOT ISNULL([Card]) ...
Like Show 0 Likes(0)

Actions
5. Re: Last Non-Null Value in a Hierarchy

paul.winiecki Jan 30, 2019 7:43 PM (in response to Joe Oppelt)

Thank you this is exactly what I needed! Was not familiar with the ISNULL function.

-Paul
Like Show 0 Likes(0)

Actions