Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

 

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;

 

6 Replies

Re: Create new column when another column has null fields

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

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!

Not applicable

Re: Create new column when another column has null fields

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;

Re: Create new column when another column has null fields

Hi,

Use Single quote in if statement instead of double quote.

Regards,

Kaushik Solanki

Not applicable

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?

Re: Create new column when another column has null fields

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

Community Browser