7 Replies Latest reply: Aug 12, 2016 4:03 PM by Jeremy Latimer RSS

    Grouping 2 variables into 1

    Kelly Boyce

      Hello -

      I'm looking to create groups that combine gender and age range.  There is a different dollar amount associated with each gender/age range combo

       

      Example:

      Female & Ages 0-1 ,

      Female & Ages 1-2,

      Female & Ages 3-5,

      Male & Ages 6-12,

      Male & Ages 13-15,

      Male & Ages 16-19 and so on...

       

      Can I create this grouping in the script?

        • Re: Grouping 2 variables into 1
          Sunny Talwar

          Sure you can, how does your raw data look like?

            • Re: Grouping 2 variables into 1
              Kelly Boyce

              [Claims]:
              LOAD "FIN_CLM_TYPE_CODE",
              MBR,
              "MBR_AGE_ON_FIRST_DOS_YRS",
              "MBR_CODE",
              "MED_CLM_CODE",
              "PAID_AMT",
              "PCP_SITE_NAME",
              "PRACTICE_GROUP_CODE",
              "PRODUCT_LINE",
              "SERVICE_DATE_TEXT",
              "SVC_CATEG_CNT_ID_CODE",
              "SVCLN_PAID_DATE",
              "SVCLN_REVERSAL_IND_CODE",
              "SVCLN_STATUS_CODE"
              Where "SERVICE_DATE_TEXT" >= '2015-01' and "SERVICE_DATE_TEXT" <= '2015-12' and "SERVICE_DATE_TEXT" <= '2015-12' and "SVCLN_STATUS_CODE" = 'PA' and "SVCLN_REVERSAL_IND_CODE" = 'N' and "SVCLN_PAID_DATE" <> '1900-01-01' and "FIN_CLM_TYPE_CODE" = 'PCP';

              SQL SELECT "FIN_CLM_TYPE_CODE",
              "MBR",
              "MBR_AGE_ON_FIRST_DOS_YRS",
              Group
              "MBR_CODE",
              "MED_CLM_CODE",
              "PAID_AMT",
              "PCP_SITE_NAME",
              "PRACTICE_GROUP_CODE",
              "PRODUCT_LINE",
              "SERVICE_DATE_TEXT",
              "SVC_CATEG_CNT_ID_CODE",
              "SVCLN_PAID_DATE",
              "SVCLN_REVERSAL_IND_CODE",
              "SVCLN_STATUS_CODE"

              Left join([Claims])
              [Member]:
              LOAD "MBR_CODE",
              "MBR_GENDER_NAME";
              SQL SELECT "MBR_CODE",
              "MBR_GENDER_NAME"

              I've bolded the fields that need to be grouped. 

                • Re: Grouping 2 variables into 1
                  Sunny Talwar

                  Would you also be able to share how the value look within those two fields that you have shared?

                  • Re: Grouping 2 variables into 1
                    Sunny Talwar

                    May be something like this:

                     

                    [Claims]:

                    LOAD "FIN_CLM_TYPE_CODE",

                      MBR,

                      "MBR_AGE_ON_FIRST_DOS_YRS",

                      "MBR_CODE",

                      "MED_CLM_CODE",

                      "PAID_AMT",

                      "PCP_SITE_NAME",

                      "PRACTICE_GROUP_CODE",

                      "PRODUCT_LINE",

                      "SERVICE_DATE_TEXT",

                      "SVC_CATEG_CNT_ID_CODE",

                      "SVCLN_PAID_DATE",

                      "SVCLN_REVERSAL_IND_CODE",

                      "SVCLN_STATUS_CODE"

                    Where "SERVICE_DATE_TEXT" >= '2015-01' and "SERVICE_DATE_TEXT" <= '2015-12' and

                      "SERVICE_DATE_TEXT" <= '2015-12' and "SVCLN_STATUS_CODE" = 'PA' and

                      "SVCLN_REVERSAL_IND_CODE" = 'N' and "SVCLN_PAID_DATE" <> '1900-01-01' and

                      "FIN_CLM_TYPE_CODE" = 'PCP';

                    SQL SELECT "FIN_CLM_TYPE_CODE",

                      "MBR",

                      "MBR_AGE_ON_FIRST_DOS_YRS",

                      Group

                      "MBR_CODE",

                      "MED_CLM_CODE",

                      "PAID_AMT",

                      "PCP_SITE_NAME",

                      "PRACTICE_GROUP_CODE",

                      "PRODUCT_LINE",

                      "SERVICE_DATE_TEXT",

                      "SVC_CATEG_CNT_ID_CODE",

                      "SVCLN_PAID_DATE",

                      "SVCLN_REVERSAL_IND_CODE",

                      "SVCLN_STATUS_CODE"

                    FROM .....;

                     

                    Left Join(Claims)

                    LOAD "MBR_CODE",

                    "MBR_GENDER_NAME";

                    SQL SELECT "MBR_CODE",

                    "MBR_GENDER_NAME"

                    FROM ....;

                     

                    FinalClaim:

                    LOAD *,

                      If(MBR_GENDER_NAME = 'Female', MBR_GENDER_NAME & ' & Ages ' &

                      If(MBR_AGE_ON_FIRST_DOS_YRS <= 2, '0-2',

                      If(MBR_AGE_ON_FIRST_DOS_YRS <= 5, '3-5')),

                      If(MBR_GENDER_NAME = 'Male', MBR_GENDER_NAME & ' & Ages ' &

                      If(MBR_AGE_ON_FIRST_DOS_YRS >= 6 and MBR_AGE_ON_FIRST_DOS_YRS <= 12, '6-12',

                      If(MBR_AGE_ON_FIRST_DOS_YRS >= 13 and MBR_AGE_ON_FIRST_DOS_YRS <= 15, '13-15',

                      If(MBR_AGE_ON_FIRST_DOS_YRS >= 16 and MBR_AGE_ON_FIRST_DOS_YRS <= 19, '16-19'))))) as NewField

                    Resident Claim;

                     

                    DROP Table Claim;