Create new column when another column has null fields

[#1]

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;

[kaushiknsolanki]

HI,

You can use the if statement and create a new column, like below.

load "PHY_TH_NPI" & '-' & "PHY_STATE_LICENSE_NUM" & '-' & "TH_TAX_ID_NUM" as "NPI_SLN_EIN",

if(isnull("PHY_TH_NPI" ) ,  PHY_STATE_LICENSE_NUM", "PHY_TH_NPI" ) as SLN,
"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;

[Oleg_Troyansky]

Something like this:

IF( LEN(TRIM(Field1)) > 0, Field1, Field2) as Field3

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy and Austin, TX!

[#4]

Thanks Kaushik Solanki,

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

load

if(isnull("PHY_TH_NPI" ) ,  PHY_STATE_LICENSE_NUM", "PHY_TH_NPI" ) as SLN,
"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;

[kaushiknsolanki]

Hi,

Use Single quote in if statement instead of double quote.

Regards,

Kaushik Solanki

[#6]

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?

[kaushiknsolanki]

Hi,

Double Quotes are used when you have space in field name.

In your case there is no space in Field Name.

Also it seems that you forgot one Double quote.

if(isnull("PHY_TH_NPI" ) ,  "PHY_STATE_LICENSE_NUM", "PHY_TH_NPI" ) as SLN,

Regards,

KaushikSolanki