topic Re: Display different label for chart in QlikView

Display different label for chart

Mon, 11 Apr 2016 14:37:27 GMT

I have a table that has "PHY_LAST_NM", "PHY_FIRST_NM", and a column that pulls in either their NPI# or State License # if NPI is blank as a new column "NPI_OR_SLN" if(isnull("PHY_TH_NPI"), "PHY_STATE_LICENSE_NUM", "PHY_TH_NPI" ) as "NPI_OR_SLN"

I have a chart that shows the top 10 PHY spend with "NPI_OR_SLN" as the Dimension and Sum of Spend as the expression. The only problem is that on the x-axis of the chart it shows the NPI or SLN# and not a name. I can't use name as the dimension because there could be multiple Physicians with the same name so "NPI_OR_SLN" is the unique identifier. Is there a way to use "NPI_OR_SLN" as the dimension but show the "PHY_LAST_NM" (or even better "PHY_FIRST_NM" "PHY_LAST_NM") on the x-axis?

Re: Display different label for chart

agni_gold — Mon, 11 Apr 2016 17:16:26 GMT

Hi David ,

There are lot of ways to do it ,

You can create one mapping table and then create one more field in your data model using apply map function. now you use new created field as dimension.

or please send sample application

Re: Display different label for chart

Mon, 11 Apr 2016 17:39:19 GMT

This is my load script.

Summary:
ODBC CONNECT TO [ASPEN QV Prod];
load
"APLCBL_GPO_MKG_PYMT_NM",
"APLCBL_GPO_MKG_PYMT_REG_ID",
"APLCBL_GPO_SBT_FLE_NM",
"APLCBL_GPO_SBT_FLE_REG_ID",
"AZ_CUST_ID",
"CHARITY_IND",
"CITY_OF_TRAVEL",
"CMS_RECORD_ID_IND",
"CNSLDIT_RPT_IND",
"CONTEXTUAL_INFO",
"COUNTRY_OF_TRAVEL",
"CVRD_RECPNT_TYPE",
"DELTA_IND",
"DELTA_MESSAGE",
"DELTA_TYPE",
"DISPUTE_IND",
"DLY_PUBLC_RESRCH_PYMT_IND",
"DT_OF_PYMT",
Month("DT_OF_PYMT") as Month_Payment,
Day("DT_OF_PYMT") as Day_Payment,
Year("DT_OF_PYMT") as Year_Payment,
"EXPENSE_ID",
"FORM_OF_PYMT",
"GNRL_PYMT_SUMM_FACT_SK",
"NATURE_OF_PYMT",
"NDC9_CD",
"NM_ASSC_CVRD_DEVCE",
"NM_ASSC_CVRD_DRUG",
"NUM_OF_PAYMT",
"ORGNL_SUBM_ID",
"PHY_FIRST_NM",
"PHY_LAST_NM",
"PHY_MIDDLE_NM",
"PHY_NM_SFX",
"PHY_OWN_IND",
"PHY_PRI_TYPE",
"PHY_SPECIALITY",
"PHY_STATE_LICENSE_NUM",
"PHY_TH_NPI",
"PROD_IND",
"RECPNT_ADDR_LINE_1",
"RECPNT_ADDR_LINE_2",
"RECPNT_CITY",
"RECPNT_COUNTRY",
"RECPNT_EMAIL",
"RECPNT_PRVNCE",
"RECPNT_PSTL_CD",
"RECPNT_STATE",
"RECPNT_ZIP_CD",
"RESUBM_FLE_IND",
"RESUBM_PYMT_REG_ID",
"ROW_LOAD_ID",
"SPD_AMT_TOTAL_PARTICIPANTS",
"STATE_OF_TRAVEL",
"TEACHING_HOSP_NM",
"TH_TAX_ID_NUM",
"THIRD_PARTY_PYMT_RECPNT_IND",
"TRD_PARTY_CVRD_RECPNT_IND",
"TRD_PARTY_ENTY_RECVNG_PYMT_NM",
if(isnull("PHY_TH_NPI"), "PHY_STATE_LICENSE_NUM", "PHY_TH_NPI" ) as "NPI_OR_SLN";
SQL SELECT *
FROM "SPD_RPT".ADMIN."GNRL_PYMT_SUMM_FACT"
WHERE row_load_id = 20160409;

Directory;
LOAD NATURE_OF_PYMT,
[Payment Category]
FROM
[Nature of payment.xlsx]
(ooxml, embedded labels, table is Sheet1);

Directory;
LOAD PHY_SPECIALITY,
PHY_SPECIALTY_NAME
FROM
Specialty.xlsx
(ooxml, embedded labels, table is Sheet1);

The current chart uses "NPI_OR_SLN" as the dimension and Sum ( "SPD_AMT_TOTAL_PARTICIPANTS") for the expression. I tried to concatenate "PHY_FIRST_NM" & "PHY_LAST_NM" & "NPI_OR_SLN" so that I could use first/last/SLN as the name but I get an error message.

Re: Display different label for chart

satishkurra — Mon, 11 Apr 2016 17:50:41 GMT

See if the attached qvw file helps

Refer General Tab in CHart title

Re: Display different label for chart

agni_gold — Mon, 11 Apr 2016 17:56:12 GMT

Load script looking perfect , and you are doing correctly , what error msg is coming

How you are writing dimension?

it is possible to send sample application ?

Re: Display different label for chart

agni_gold — Mon, 11 Apr 2016 18:05:04 GMT

PFA , with sample data

Re: Display different label for chart

Mon, 11 Apr 2016 18:25:24 GMT

This is the script error message I get for the below script. I will take a look at the attached files and see if they help.

Script error message:

Field not found - <NPI_OR_SLN>

SQL SELECT *

FROM "SPD_RPT".ADMIN."GNRL_PYMT_SUMM_FACT"

WHERE row_load_id = 20160409

This is the script I used:

Summary:

ODBC CONNECT TO [ASPEN QV Prod];

load

"APLCBL_GPO_MKG_PYMT_NM",

"APLCBL_GPO_MKG_PYMT_REG_ID",

"APLCBL_GPO_SBT_FLE_NM",

"APLCBL_GPO_SBT_FLE_REG_ID",

"AZ_CUST_ID",

"CHARITY_IND",

"CITY_OF_TRAVEL",

"CMS_RECORD_ID_IND",

"CNSLDIT_RPT_IND",

"CONTEXTUAL_INFO",

"COUNTRY_OF_TRAVEL",

"CVRD_RECPNT_TYPE",

"DELTA_IND",

"DELTA_MESSAGE",

"DELTA_TYPE",

"DISPUTE_IND",

"DLY_PUBLC_RESRCH_PYMT_IND",

"DT_OF_PYMT",

Month("DT_OF_PYMT") as Month_Payment,

Day("DT_OF_PYMT") as Day_Payment,

Year("DT_OF_PYMT") as Year_Payment,

"EXPENSE_ID",

"FORM_OF_PYMT",

"GNRL_PYMT_SUMM_FACT_SK",

"NATURE_OF_PYMT",

"NDC9_CD",

"NM_ASSC_CVRD_DEVCE",

"NM_ASSC_CVRD_DRUG",

"NUM_OF_PAYMT",

"ORGNL_SUBM_ID",

"PHY_FIRST_NM",

"PHY_LAST_NM",

"PHY_MIDDLE_NM",

"PHY_NM_SFX",

"PHY_OWN_IND",

"PHY_PRI_TYPE",

"PHY_SPECIALITY",

"PHY_STATE_LICENSE_NUM",

"PHY_TH_NPI",

"PROD_IND",

"RECPNT_ADDR_LINE_1",

"RECPNT_ADDR_LINE_2",

"RECPNT_CITY",

"RECPNT_COUNTRY",

"RECPNT_EMAIL",

"RECPNT_PRVNCE",

"RECPNT_PSTL_CD",

"RECPNT_STATE",

"RECPNT_ZIP_CD",

"RESUBM_FLE_IND",

"RESUBM_PYMT_REG_ID",

"ROW_LOAD_ID",

"SPD_AMT_TOTAL_PARTICIPANTS",

"STATE_OF_TRAVEL",

"TEACHING_HOSP_NM",

"TH_TAX_ID_NUM",

"THIRD_PARTY_PYMT_RECPNT_IND",

"TRD_PARTY_CVRD_RECPNT_IND",

"TRD_PARTY_ENTY_RECVNG_PYMT_NM",

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

"TEACHING_HOSP_NM"&'.'&"TH_TAX_ID_NUM" as "TH_Unique_Name",

"PHY_FIRST_NM"&'.'&"PHY_LAST_NM"&'.'&"NPI_OR_SLN" as "HCP_Unique_Name";

SQL SELECT *

FROM "SPD_RPT".ADMIN."GNRL_PYMT_SUMM_FACT"

WHERE row_load_id = 20160409;

Directory;

LOAD NATURE_OF_PYMT,

[Payment Category]

FROM

[Nature of payment.xlsx]

(ooxml, embedded labels, table is Sheet1);

Directory;

LOAD PHY_SPECIALITY,

PHY_SPECIALTY_NAME

FROM

Specialty.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: Display different label for chart

agni_gold — Mon, 11 Apr 2016 19:54:55 GMT

Try this

"PHY_FIRST_NM"&'.'&"PHY_LAST_NM"&'.'&if(isnull("PHY_TH_NPI"), "PHY_STATE_LICENSE_NUM", "PHY_TH_NPI" ) as "HCP_Unique_Name";