Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Sure you can, how does your raw data look like?
[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.
Would you also be able to share how the value look within those two fields that you have shared?
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?
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;
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.
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