2 Replies Latest reply on Nov 8, 2018 9:51 AM by Nirvan Hulangamuwa

    How to create a calculated field that'll include the not null version of the data in a field

    Nirvan Hulangamuwa

      Hello,

       

      I have a data set that has a some null values (see below sample)

       

        

      CITYREGION
      THORNTONCENTRAL
      TORONTOCENTRAL
      TORONTONULL
      TOTTENHAMCENTRAL
      WAUBAUSHENECENTRAL
      WHITBYEASTERN
      WHITBYEASTERN

       

       

      Is there a way to create  a calculated field like below? 

         

      CITYREGIONCALCULATED FIELD (REGION CLEANED)
      THORNTONCENTRALCENTRAL
      TORONTOCENTRALCENTRAL
      TORONTONULLCENTRAL
      TOTTENHAMCENTRALCENTRAL
      WAUBAUSHENECENTRALCENTRAL
      WHITBYEASTERNEASTERN
      WHITBYNULLEASTERN

       

      I unfortunately can't exclude the null values as there are other data in those rows, and when I try to group them, the null group changes with other group

      (EG; From the above sample, if Toronto gets groups to Central, all the NULLS will be grouped to CENTRAL, including whitby, which should be EASTERN)

       

       

      Thanks!