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

# Last Non-Null Value in a Hierarchy

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

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

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
• ###### 2. Re: Last Non-Null Value in a Hierarchy

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
• ###### 3. Re: Last Non-Null Value in a Hierarchy

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
• ###### 4. Re: Last Non-Null Value in a Hierarchy

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]) ...

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

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

-Paul