Create new column when another column has null fields
I have a large data source that I need to do analysis on. The problem is that I do not have a consistent unique identifier for each Physician (PHY). The PHY will either have an NPI ("PHY_TH_NPI"), an SLN ("PHY_STATE_LICENSE_NUM") or an EIN ("TH_TAX_ID_NUM"). The below script works for me by concatenating these three columns into 1 so that give me a unique identifier. The only issue I found is that some physicians can have a NPI (123456) and 2 different SLNs (DE1234 & MD4567) so one physician would then have two unique identifiers (123456-DE1234 and 123456-MD4567). I want to create a new column called "SLN" and only pull the "PHY_STATE_LICENSE_NUM" into that column IF the "PHY_TH_NPI" column is blank. Then I can do the concatenate below but use the new "SLN" instead of the "PHY_STATE_LICENSE_NUM".
I added if(isnull("PHY_TH_NPI" ) , PHY_STATE_LICENSE_NUM", "PHY_TH_NPI" ) as SLN, and I now have the column with the correct number for NPI and State Lic Num as the new SLN.
How do I now concatenate the new SLN column and the existing "TH_TAX_ID_NUM" column? I tried this... "SLN" & '-' & "TH_TAX_ID_NUM"as"NPI_SLN_EIN", but I get a error that SLN field is not found. Here is my current script
Re: Create new column when another column has null fields
The double quotes are added automatically when I select the columns from the table.
Right now, this line (if(isnull("PHY_TH_NPI" ) , PHY_STATE_LICENSE_NUM", "PHY_TH_NPI" ) gives me the SLN when the NPI is blank. This is perfect and exactly what I need but I also need the TH Tax ID Num when both the SLN and NPI are blank. Is there a way to nest another if statement in this statement. Or can I concatenate the newly created column "SLN" with the existing TH Tax ID Num?