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

       

       

      ODBC CONNECT TO [ASPEN QV Prod];
      load "PHY_TH_NPI" & '-' & "PHY_STATE_LICENSE_NUM" & '-' & "TH_TAX_ID_NUM" as "NPI_SLN_EIN",
      "APLCBL_GPO_MKG_PYMT_NM",
      "AZ_CUST_ID",
      "CITY_OF_TRAVEL",
      "CMS_RECORD_ID_IND",
      "DT_OF_PYMT",
      "EXPENSE_ID",
      "FORM_OF_PYMT",
      "NATURE_OF_PYMT",
      "PHY_FIRST_NM",
      "PHY_LAST_NM",
      "PHY_MIDDLE_NM",
      "PHY_NM_SFX",
      "PHY_PRI_TYPE",
      "PHY_SPECIALITY",
      "PHY_STATE_LICENSE_NUM",
      "PHY_TH_NPI",
      "ROW_LOAD_ID",
      "SPD_AMT_TOTAL_PARTICIPANTS",
      "STATE_OF_TRAVEL",
      "TEACHING_HOSP_NM",
      "TH_TAX_ID_NUM";
      SQL SELECT *
      FROM "SPD_RPT".ADMIN."GNRL_PYMT_SUMM_FACT"
      WHERE row_load_id = 20160207;