2 Replies Latest reply: Mar 7, 2017 8:33 AM by Tracy Garon Hojka RSS

    Filling in Null Value Fields Using Calculated Field

    Tracy Garon Hojka

      I'm working in Data Prep Studio on an appended table that is sourced by 20 separate text based files.  I found that one field, called the Ticker Symbol, is sometimes NULL because the security in question does not trade using a ticker.  However, other identifiers exist for this security and appear within the table as one of two other fields (either the CUSIP or the Custodian ID #).  I'm looking for a simple formula for a calculated field that will fill in the NULLs by copying either the CUSIP or the Custodian ID # to the "Final Ticker Symbol" field.  The trick is, sometimes both the Ticker Symbol and the CUSIP fields are NULL.  So I guess what I'm asking is...is there a way to set up a multiple level 'If Statement' in the calculated fields to do the following?

       

      If Ticker Symbol is NULL, then

          Insert CUSIP, unless CUSIP is NULL, else

          Insert Custodian ID


      Your help is greatly appreciated!


      Thanks!  Tracy Garon Hojka, Whitnell & Co.