6 of 6 people found this helpful
Ah, yes. You can wrap those fields in IIF(ISNULL([End User]),"",[End User]) to swap in empty strings.
IIF(ISNULL([End User]),".,",[End User])+
IIF(ISNULL([End User Country]),".,",[End User Country] )+
IIF(ISNULL([End User State]),".,",[End User State] )+
IIF(ISNULL([End User City]),".",[End User City] )
if you want to show that the NULL values were evaluated to NULL?
So you get a result of
if my country is missing. No?
Peace and All Good!
Can the IIF ISNULL function be used for date formats? I am attempting make a date column replace NULL with blanks with:
IIF(ISNULL([Blast Date]),"",[Blast Date])
but I get the following error:
"IIF is being called with (boolean,string,date), it should be called with (boolean,float,float) or (boolean,integer,integer) or (boolean,datetime,datetime) or (boolean,date,date) or (boolean,boolean,boolean)"
I don't know what to put in the second phrase to match the (boolean,date,date) requirement. I just want the cell to be blank if there's no value in the Blast Date column. Help?
Did some testing and here is what I found so far, using an Excel data source, so take that as a caveat. James, beat me up if I am way off base:
Look at the attached data_testnulldate.PNG to see the data I used. Notice the missing date in row 5 and row 14. From what I read somewhere, Tableau considers the first 8 rows of a data source to scope out the schema, so, in this case, it sees the first column as a date field, sure enough.
To mimic your situation, I have this calculated field set up: [testnulldate] = iif(isnull([date]),'',[date]). Note the single quotes instead of the double quotes <- this is very important.
The attached image testnulldate.png shows the results. The dimensions [date] and [testnulldate] are on the row shelf and SUM([data]) is on the text shelf. Note that for the row 5 record above, [date] is Null in the viz, and blank in row for the row 14 record. However, using the calculated field [testnulldate] you can get the visual result you want in the table, the dashboard viz shown in nulldateviz.png shows how data for these null date records might behave in a sample viz.
Going to leave it to James Baker and other Tableauians to explain why all this is as it is, but, unless this approach screws up something else you are trying to do with the viz, there you are.
Peace and All Good!
Hi All the first comment was exactly what I needed, however if I have changed the aliases to a field I am concatenating, by default it places the original values into the calculated field. Is there a way to have the new aliases come through?
how to concatenate a concatenated value to another concatenated value in three fields in a tableau work ?
I'm 5 years late to this thread but I don't think you can replace null strings with empty strings ("") as an empty string is by definition a null string so will not concatenate in Tableau. For example, IFNULL([STR1], "")+"_"+IFNULL([STR2],"") will be NULL if either STR1 or STR2 is NULL. Any other ideas other than explicit IF statements for every combination of possible NULL values in a three part concatenation?
Sorry Zach I misread your post. Can't you work it out with an IF statement using ISNULL and IFNULL in a nested fashion. It wouldn't be 'explicit' for all combos, but cascading.
Thanks Shawn. I could use a nested IF statement and I started putting that together but it made my head hurt so I came up with the following more elegant solution:
// construct a compound string for searching lists of exact combinations of these three strings
// end users will be constructing similar strings in Excel for pasting into Tableau filters
// [P5_SEQ]-[P7_SEQ]-[IS1_SEQ], e.g. CTAG--, ACTG-AATT- or --GTCA
// difficulty is that some of these are always null and Tableau won't concatenate null strings
// even IFNULL doesn't work because an empty string ("") is considered a null string by Tableau
// e.g. IFNULL([P5_SEQ],"")+"-"+IFNULL([P7_SEQ],"")+"-"+IFNULL([IS1_SEQ],"")
// so use X for the null substitute then strip it at the end as X is not a valid character for these strings
REPLACE(IFNULL([P5_SEQ],"X")+"-"+IFNULL([P7_SEQ],"X")+"-"+IFNULL([IS1_SEQ],"X"), "X", "")
This wins the oldest thread found and marked 'helpful'! 8 years! Oh, my! James Baker has always been 'the man'.