3 Replies Latest reply on Jun 25, 2019 2:07 PM by trevor.blackwell

    Rename Nulls in Data Blending

    Benjamin Wong
    Benjamin Wong Jun 25, 2013 3:56 AM

      Hi,

      I have a primary sql database (say, D1) joining to a secondary Excel spreadsheet D2. D1 has a unique ID for each row. D2 contains a subset of those IDs and a Comments column (which could be blank). Now what I want to do is join them together so that I see all the rows in D1, and the associated Comment from D2. When I do this, I get a Null comment where the row doesnt exist in D2. This is expected, but how can I rename the Null to just a blank space or even 'No Comment'? Ifnull and isnull don't work.

       

      Eg in the below, I've created a calculated Comments2 which uses Ifnull to return "D2 empty cell". This works where there is a matching ID in D2 but no comment. But you can see that in the last 6 rows, there is no matching ID in D2 at all so ifnull doesn't work here. Basically I would like all of the Nulls replaced.

      comments.jpg

       

      I'm in Tableau 6.1.

       

      thanks

        • 1. Re: Rename Nulls in Data Blending
          Benjamin Wong
          Benjamin Wong Jun 25, 2013 4:12 AM (in response to Benjamin Wong)

          OK, so I've solved it! For some reason, if I create the Comments2 caluclated field within the D2 datasource, I get the above behaviour. But if I create the same calculated field but within the D1 datasource instead, then all the nulls get replaced. Could someone please explain why this is the case?

          1 of 1 people found this helpful
          • 2. Re: Rename Nulls in Data Blending
            chris.jeanty
            chris.jeanty Feb 23, 2016 11:06 AM (in response to Benjamin Wong)

            Think of blending as a left join, where your secondary table left joins on your primary table.  The ifnull() logic then cannot differentiate between nulls that are due to your logic, are nulls that occur because there were no matches, so it returns all nulls.  Applying ifnull() to the primary source, as you did, will function appropriately as the primary table logics' need not deal with that aforementioned ambiguity.

             

            Hope that makes sense.

            • 3. Re: Rename Nulls in Data Blending
              trevor.blackwell
              trevor.blackwell Jun 25, 2019 2:07 PM (in response to Benjamin Wong)

              Just change the blanks in the master data. I never let Tableau do this. All blanks = #N/A in excel file using conditional formatting in under 10 seconds.