6 Replies Latest reply: Feb 29, 2016 2:41 PM by Kaushik Solanki RSS

    Create new column when another column has null fields

    David Kwiatkowski

      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".



      load "PHY_TH_NPI" & '-' & "PHY_STATE_LICENSE_NUM" & '-' & "TH_TAX_ID_NUM" as "NPI_SLN_EIN",
      SQL SELECT *
      WHERE row_load_id = 20160207;