5 Replies Latest reply: Oct 17, 2012 12:42 PM by John Witherspoon RSS

    Subfield

    Hardik Gandhi

      Hi,

       

      I have a customer table holding the information for all the departments:

       

      Department:

      ABC - 1234

      XYZ - 1234456

      Null

      QVW - 98712

       

      I am extracting the fund information from the department as:

      SubField(Department,'-',1) as Fund

       

      Everything works fine here but I would like to rename Null as 'N/A' when I extract the fund information using

      SubField(Department,'-',1) as Fund,

       

      Regards,

      H

        • Re: Subfield
          Stefan Wühl

          Try

           

          replace(subfield(Department,'-',1),'Null','N/A') as Fund

            • Re: Subfield
              Hardik Gandhi

              It is not working. It still displays as empty field.

                • Re: Subfield
                  Jefferson Martins

                  Hi

                   

                  You can use isnull() function to do this

                   

                  if ( not isnull(Department), subfield(Department,'-',1), 'N/A'as Fund

                   

                   

                  Regards

                  • Re: Subfield

                    Try If(Len(Department) <> 0, SubField(Department,'-',1), 'N/A') as Fund,

                    • Re: Subfield
                      John Witherspoon

                      Works fine for me IF by 'Null' you mean the string 'Null'.  So I'm betting you meant the field is actually null.  Then perhaps this:

                       

                      if(len(subfield(Department,'-',1)),subfield(Department,'-',1),'N/A') as Fund

                       

                      Is there a string version of the alt() function?  That would be simpler if so, but I'm not aware of one and don't see anything promising in the list of string functions.  Maybe there's some other way to simplify it, though.

                       

                      Edit: I was too slow with my post, and indeed my expression was more complicated than it needed to be.  You can slightly simplify what Ducati 888 SP5 wrote, though, since 0 is false:

                       

                      if(len(Department),subfield(Department,'-',1),'N/A') as Fund

                       

                      Though I suppose that if the value of Department was '- Something', you might need my more-complicated version.  I'm guessing you don't have data like that, though.