7 Replies Latest reply: Apr 11, 2016 3:54 PM by Agnivesh Kumar RSS

    Display different label for chart

    David Kwiatkowski

      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
          Agnivesh Kumar

          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
              David Kwiatkowski

              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
                  Agnivesh Kumar

                  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
                      David Kwiatkowski

                      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
                      Agnivesh Kumar

                      PFA , with sample data

                  • Re: Display different label for chart
                    Satish Kurra

                    See if the attached qvw file helps

                     

                    Refer General Tab in CHart title