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

Missing age groups

I am designing a QV app using data that I loaded in QV from an excel spreadsheet. The spreadsheet lists individuals with their ages. I tried to create age groups in QV scripting using the  age field and "if" statement but some of the individuals in the data were not assigned to any age group even though their age is included in the data both in QV and in the excel spreadsheet. How do I fix these missing age groups? could there be something wrong with the scripting? the script loaded fine.

LOAD *,

if([CMED Age] >20, 'Over 20',

if([CMED Age] <= 20 and [CMED Age] >= 16, '16 - 20',

if([CMED Age] <= 15 and [CMED Age] >= 11, '11 - 15',

if([CMED Age] <= 10 and [CMED Age] >= 6, '6 - 10',

if([CMED Age] <= 5 and [CMED Age] >= 4, '4 - 5',

if([CMED Age] <= 3 and [CMED Age] >= 1, '1 - 3',

if([CMED Age] < 1, 'Under 1'))))))) as [CMED Age_Group];

Thanks

1 Solution

Accepted Solutions
maxgro
MVP
MVP

floor (or round or ceil) the age

or change the limit

LOAD *,

if([CMED Age] >20, 'Over 20',

if([CMED Age] <= 20 and [CMED Age] > 15,      '>15 - 20',

if([CMED Age] <= 15 and [CMED Age] > 10,      '>10 - 15',

if([CMED Age] <= 10 and [CMED Age] > 5,        '>5 - 10',

if([CMED Age] <= 5 and [CMED Age] > 3,          '>3 - 5',

if([CMED Age] <= 3 and [CMED Age] >= 1,        '1 - 3',

if([CMED Age] < 1,                                           'Under 1'

)))))))

as [CMED Age_Group];

View solution in original post

13 Replies
pamaxeed
Partner - Creator III
Partner - Creator III

Have you tried like this:

LOAD *,

if([CMED Age] >20, 'Over 20',

if([CMED Age] <= 20 and [CMED Age] >= 16, '16 - 20',

if([CMED Age] <= 15 and [CMED Age] >= 11, '11 - 15',

if([CMED Age] <= 10 and [CMED Age] >= 6, '6 - 10',

if([CMED Age] <= 5 and [CMED Age] >= 4, '4 - 5',

if([CMED Age] <= 3 and [CMED Age] >= 1, '1 - 3',

if([CMED Age] < 1, 'Under 1', 'Missing Group'))))))) as [CMED Age_Group];

Not applicable
Author

But that would tell it to assume that the age group is missing for that individual right? which would be incorrect because the individual does have an age in the age field and so, should have an age group among those listed in the script, rather than being defined as "missing group". I can't seem to understand why some of the individuals were not grouped into one of the age groups listed - that's what I would like to fix...

Digvijay_Singh

Can you share the sample data if possible?

Digvijay_Singh

Also check possibility of using class function QV provides to create buckets like this.

Not applicable
Author

Here's what the data looks like:

   

Name DOB Age (yrs)
Name14/27/20123
Name28/24/20105
Name312/31/20078
Name41/14/20142
Name510/10/20078
Name611/22/20105
maxgro
MVP
MVP

maybe you have decimal in some age?

10.5

15.5

Not applicable
Author

That's just what I was thinking because the age field was a calculated field in excel using the date of birth. So, what's the best way to resolve that?

Not applicable
Author

I noticed that the people that do not have an age group assigned have an age at either the lower or higher end of each age group

pamaxeed
Partner - Creator III
Partner - Creator III

You can try to format it like that num(text(CMED_AGE))....