topic Re: Grouping 2 variables into 1 in QlikView

Grouping 2 variables into 1

Fri, 12 Aug 2016 17:21:02 GMT

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 — Fri, 12 Aug 2016 17:34:16 GMT

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

Re: Grouping 2 variables into 1

Fri, 12 Aug 2016 17:43:21 GMT

[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 — Fri, 12 Aug 2016 17:52:41 GMT

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

Fri, 12 Aug 2016 17:57:01 GMT

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?

Re: Grouping 2 variables into 1

sunny_talwar — Fri, 12 Aug 2016 18:01:07 GMT

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;

Re: Grouping 2 variables into 1

Fri, 12 Aug 2016 19:17:11 GMT

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.

Re: Grouping 2 variables into 1

Anonymous — Fri, 12 Aug 2016 20:03:13 GMT

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