topic Re: Create new column when another column has null fields in QlikView

Create new column when another column has null fields

Thu, 18 Feb 2016 19:19:00 GMT

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;

Re: Create new column when another column has null fields

kaushiknsolanki — Thu, 18 Feb 2016 19:33:40 GMT

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;

Re: Create new column when another column has null fields

Oleg_Troyansky — Thu, 18 Feb 2016 19:33:52 GMT

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!

Re: Create new column when another column has null fields

Mon, 29 Feb 2016 13:36:12 GMT

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

Re: Create new column when another column has null fields

kaushiknsolanki — Mon, 29 Feb 2016 18:50:02 GMT

Hi,

Use Single quote in if statement instead of double quote.

Regards,

Kaushik Solanki

Re: Create new column when another column has null fields

Mon, 29 Feb 2016 19:25:53 GMT

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?

Re: Create new column when another column has null fields

kaushiknsolanki — Mon, 29 Feb 2016 19:41:36 GMT

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