11 Replies Latest reply on Jun 9, 2016 4:32 PM by Shawn Wallwork

    Concatenate Calculation

    . tpatriquin

      Created a calculation to concatenate a few different fields

       

      [End User]+", "+[End User Country]+", "+[End User State]+", "+[End User City]

       

      If one of these values is null the end result for the entire calculation is null. Is there a way I can change this to where if a value is null the end result isn't so drastically affected?

        • 1. Re: Concatenate Calculation
          James Baker

          Ah, yes.  You can wrap those fields in IIF(ISNULL([End User]),"",[End User]) to swap in empty strings.

          6 of 6 people found this helpful
          • 3. Re: Concatenate Calculation
            Michael Cristiani

            Alternatively:

             

            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

             

            Me,.,OH,Cincinnati

             

            instead of

             

            MeOHCincinnati

             

            if my country is missing.  No?

             

            MANY BLESSINGS!

            Peace and All Good!

            Michael W Cristiani

            Market Intelligence Group

            • 4. Re: Concatenate Calculation
              Lea Sp

              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?

              • 5. Re: Concatenate Calculation
                Michael Cristiani

                Lea,

                 

                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.

                 

                MANY BLESSINGS!

                Peace and All Good!

                Michael W Cristiani

                Market Intelligence Group, LLC

                • 6. Re: Concatenate Calculation
                  guest contributor

                  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?

                   

                  Thanks

                   

                  Chris

                  • 7. Re: Concatenate Calculation
                    Srinivas Anupoju

                    Hi ,

                     

                    how to concatenate a concatenated value to another concatenated value in three fields in a tableau work ?

                     

                    Thanks

                    Srini

                    • 8. Re: Re: Concatenate Calculation
                      Zach Leber

                      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?

                      • 9. Re: Concatenate Calculation
                        Shawn Wallwork

                        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.

                         

                        --Shawn

                        • 10. Re: Re: Concatenate Calculation
                          Zach Leber

                          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", "")

                          • 11. Re: Concatenate Calculation
                            Shawn Wallwork

                            This wins the oldest thread found and marked 'helpful'! 8 years! Oh, my! James Baker has always been 'the man'.

                             

                            --Shawn