Filling in Null Value Fields Using Calculated Field

Altair Forum User

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.

Altair Forum User

Hi Tracy,

 

I think the following will do it:

 

If(IsNull(Ticker),

    If(IsNull(CUSIP),[CustodianID],CUSIP),

    Ticker)

 

The logic is:  If Ticker is null, then take a look at CUSIP.  If that is null, then use the Custodian ID, if it's not then use CUSIP.

 

Regards,

Steve,

Altair Forum User

Altair Forum User said:

Hi Tracy,

 

I think the following will do it:

 

If(IsNull(Ticker),

    If(IsNull(CUSIP),[CustodianID],CUSIP),

    Ticker)

 

The logic is:  If Ticker is null, then take a look at CUSIP.  If that is null, then use the Custodian ID, if it's not then use CUSIP.

 

Regards,

Steve,

Elegant and simple!  Just what I was looking for.  Thank you Steve!