2 Replies Latest reply on Apr 15, 2019 6:52 AM by michael.tarne

    Replacing Null values based on another field

    michael.tarne

      Hi all, I'm having an issue with replacing null values in data like this:

       

      ContinentCountry
      Asia

      China

      AfricaEgypt
      Germany
      North AmericaCanada

       

      I'm trying to use a nested if statement like this to fix it:

       

      If [Continent] = '' THEN

           (IF [Country] = 'Germany' or [Country] = "France" THEN [Continent] = "Europe" ELSEIF [Country] = "Japan" OR [Country] = "Mongolia" THEN [Continent] = 'Asia' END) END

       

      But it's just returning null, despite the correct values being in the list of string it's checking for. Any thought on how to get it working correctly?

        • 1. Re: Replacing Null values based on another field
          Joshua Milligan

          Hi Michael,

           

          Is Continent blank or NULL?  If it is blank, then your calculation should work, but if it is NULL, then [Continent] = '' will false and you should use something like ISNULL([Continent]).  If it could be blank or NULL, you might consider something like:

           

          IFNULL([Continent], '') = ''

           

          That will force the NULL values to be changed to blank or it will simply take the value (including blank) and then compared to blank.

           

          You might also consider getting a data source that is simply a list of unique countries with continents and joining it into the flow on Country.  Then, you can use the continent that comes from matches on country and not worry if the field in your data source is blank or null or not.

           

          Hope that helps!

          Joshua

          1 of 1 people found this helpful
          • 2. Re: Replacing Null values based on another field
            michael.tarne

            Got it working, thanks! I did end up re-doing the flow.