Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Grouping 2 variables into 1

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

Not applicable

Re: Grouping 2 variables into 1

[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

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

Not applicable

Re: Grouping 2 variables into 1

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

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

Re: Grouping 2 variables into 1

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.

Community Manager
Community Manager

Re: Grouping 2 variables into 1

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

Community Browser