Here's one that really twists my head around.
I frequently need to assign a placeholder value to a field when the Tableau interprets the data value as NULL. In most cases it's assigning a zero to a null value.
Using a calculated field as a proxy for the real data field allows me to do this, and all I need is a way to assign the value zero (0) to the calculated field when the data value is null.
The logic of this is:
to the calculated field:
if the data field is null assign zero
else assign the data field's value
The Tableau IFNULL()function does this, and in exactly the way that it should.
Here's the Tableau calculated field dialog showing IFNULL() (edited for space):
Spot the problem?
The description of the function is the accurate description of the function's behavior. When the data field's value is NOT null, its value is used for the calculated field, otherwise the value of expr2 provides the value. The function's description and behavior match each other, and are exactly what we need.
But the function's name is wrong.
It should be IFNOTNULL
—NOT—
IFNULL
This misnaming is at least confusing, and has the potential to lead people to making poor or outright wrong decisions when they rely on the results of employing it based on the semantics of its name.
Oddly, the logically inverted naming makes it harder for experienced programmers to find the right function to use than for inexperience people.
Two more not-null situations:
ReplyDelete- the concept of "Missing" when Tableau performs domain padding. That cannot be directly detected.
- the "Special" value ATTR() function returns and displays as an *.
Also what about the fourth argument in the IIF() function, the "unknown" result. Why is this not an option in an IF statement?
Excellent points, Joe.
DeleteCoding Tableau's calculated fields is frustrating. It's not really a programming language, not even a proper mini- or scripting language.
Your examples show a problem with the data instantiation model, no "Missing"; and with exposing the results of function evaluation.
My example in this post is particularly vexing to me because it demonstrates that whomever implemented this function, or whomever was responsible for its inclusion missed the fact that its behavior is the opposite of its name's meaning. Learning how to express the correct logical form is one of the first steps in learning how to program, and in general problem solving. The distinction between IS and IS-NOT is fundamental, one that absolutely must be mastered.
There are plenty of other gaps and inconsistencies in the field calculation constructs.
Not to mention the big, baldly obvious problems with the calculation editor itself. The one that jumps to mind is: upon selecting one of the complex functions the editor pastes only a portion of it into the calculation editing space - AND - removes the example from the lower-right display. This leaves the user without a full code block to work with, and no reference code to be his/her guide, so one has to flip back and forth. Unless one has an eidetic memory, which is very uncommon. Hmmmm, gives me an idea for a post...
How about "coalesce()"
ReplyDeleteI'd like to see IFNULL renamed to this. Also, I understand & respect Chris' point of view, but I think it comes down to how you're accustomed to reading that type of logic. Given my PHP background, IFNULL by default communicates the following pseudo:
Delete"If Expression1 is null then Expression2"
Tableau documents this in a long winded way, which makes it awkward to read & digest for many I'm sure.
I don't think I agree with the original premise. It is a semantic choice to do it this way and you could argue that IFNOTNULL would be just as illogical.
ReplyDeleteIFERROR in Excel has the same grammar as Tableau's IFNULL:
=IFERROR(value, value_if_error)
"If Sales is null, then return 100"
Sorry, Andy, I'm sticking to my position.
DeleteFrom a human standpoint, the reader has to figure out: what, exactly, is being evaluated to see if it's null, what positive action is being taken when it is null, and what positive action is being taken when it's not.
If I have it right (it's been a long time since undergraduate logic class), the two semantic constructions for {FUNCTION}(,) are:
1) If IS NOT NULL, then return else return
2) If IS NULL then return else return
My position is that 1) is better than 2), and the documentation describes 1), although it's not as clearly worded as it could be.
Parsing 1) is relatively straightforward, it only requires backtracking to the last seen object.
Parsing 2) is much twistier, it requires a forward leapfrog upon IS NULL, followed by a backreference when IS NOT NULL.
I'm sure someone could do a better job on the logical formalism.
Also, Tableau's IFNULL() works the same way as MySQL IFNULL() and T-SQL ISNULL().
DeleteIt would be profoundly confusing if Tableau's IFNULL worked exactly the opposite of Excel, MySQL, and SQL Server.
Continuing badness isn't a virtue.
DeleteIt's interesting how different people interpret things differently. I read IFNULL(A, B) as "If A is NULL, return B" and it's always made perfect sense to me that way. (Also think that was the original intention.) If it were the other way around I would find it confusing, since I would read IFNOTNULL(A ,B) as "If A is NOT NULL, return B".
ReplyDeleteHow do IF NULL Function with respect to Date Fields ?
ReplyDelete