Display different label for chart

[#1]

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?

[agni_gold]

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

[#3]

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.

[satishkurra]

See if the attached qvw file helps

Refer General Tab in CHart title

[agni_gold]

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 ?

[agni_gold]

PFA , with sample data

[#7]

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);

[agni_gold · Accepted solution]

Try this

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