Appointment criteria filter

[cjett7769]

Hello,

I am trying to build an application that shows when a person turned 18 and if they had an appointment within the past 10 years once they turned 18. Any help would surely be appreciated.

[Gysbert_Wassenaar]

Please read these two documents:

• QlikCommunity Tip: How to get answers to your post?
• Preparing examples for Upload - Reduction and Data Scrambling

[sunny_talwar]

How does your underlying data look like. Are you trying to create a flag in the script or is this a thing to do on the front end? If possible provide some data with expected output

[cjett7769]

Thanks Sunny for the help.

LOAD DOB,
"FULL_NM",
"PAT_KEY",
PAT_AGE,
"AGE_Group",
"APT_DATE",
"DATE_WHEN_18",
"PAT_MRN_ID",
Date(AddYears(DOB, 18)) as Turned18;
SQL SELECT DOB,
extract (year from (age(current_date,dob))) as PAT_AGE,
case
when extract (year from (age(current_date,dob))) >=18 then '18 or older'
when extract (year from (age(current_date,dob))) <18 then 'Younger then 18'
end AS "AGE_Group",

case
when (add_months(DOB,216)) < current_date then 'DATE_WHEN_18'
when (add_months(DOB,216)) > current_date then 'NOT_18'
end as "DATE_WHEN_18",
"FULL_NM",
"PAT_KEY",
"PAT_MRN_ID" ,

"APT_DATE",

[sunny_talwar]

May be something like this:

Table:

LOAD DOB,
"FULL_NM",
"PAT_KEY",
PAT_AGE,
"AGE_Group",
"APT_DATE",
"DATE_WHEN_18",
"PAT_MRN_ID",
Date(AddYears(DOB, 18)) as Turned18;
SQL SELECT DOB,
extract (year from (age(current_date,dob))) as PAT_AGE,
case
when extract (year from (age(current_date,dob))) >=18 then '18 or older'
when extract (year from (age(current_date,dob))) <18 then 'Younger then 18'
end AS "AGE_Group",

case
when (add_months(DOB,216)) < current_date then 'DATE_WHEN_18'
when (add_months(DOB,216)) > current_date then 'NOT_18'
end as "DATE_WHEN_18",
"FULL_NM",
"PAT_KEY",
"PAT_MRN_ID" ,

"APT_DATE",

Left Join(Table)

LOAD PAT_KEY,

          If(Turned18 <= Today() and Max(APT_DATE) <= AddYears(Today(), -10), 1, 0) as Flag

Resident Table

Group By PAT_KEY, Turned18;

[sunny_talwar]

Flag may not be right. Can you elaborate on this:

"if they had an appointment within the past 10 years once they turned 18"

Are we checking that after they turned 18, do they have an appointment in last 10 years?