Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Grouping 2 variables into 1

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?

7 Replies
sunny_talwar

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

Not applicable
Author

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

sunny_talwar

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

Not applicable
Author

MBR_GENDER_NAME : "Female" or "Male" text field

MBR_AGE_ON_FIRST_DOS_YRS : {0,1,2,..) number field

I would like to produce a Dollar Amount for each Combo I specified earlier...

I'm not sure if this the info you are looking for?

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;

Not applicable
Author

Used this but I tweaked a few things.  I applied the grouping code to the mbr age only and then will apply a gender name filter in the front end.

Otherwise, the code worked great!  Thank you for your help.

Anonymous
Not applicable
Author

Hi Kelly,

We're glad to see you were able to solve your issue. Please take a moment to Mark Replies as Correct or Helpful to give points to those who helped and to mark the thread as Answered.

Regards,

Qlik Community Team