topic Re: Missing age groups in QlikView

Missing age groups

Wed, 23 Sep 2015 18:34:02 GMT

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

Re: Missing age groups

pamaxeed — Wed, 23 Sep 2015 19:07:39 GMT

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];

Re: Missing age groups

Wed, 23 Sep 2015 19:14:25 GMT

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...

Re: Missing age groups

Digvijay_Singh — Wed, 23 Sep 2015 19:18:47 GMT

Can you share the sample data if possible?

Re: Missing age groups

Digvijay_Singh — Wed, 23 Sep 2015 19:20:53 GMT

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

Re: Missing age groups

Wed, 23 Sep 2015 19:23:44 GMT

Here's what the data looks like:

Name	DOB	Age (yrs)
Name1	4/27/2012	3
Name2	8/24/2010	5
Name3	12/31/2007	8
Name4	1/14/2014	2
Name5	10/10/2007	8
Name6	11/22/2010	5

Re: Missing age groups

maxgro — Wed, 23 Sep 2015 19:34:12 GMT

maybe you have decimal in some age?

10.5

15.5

Re: Missing age groups

Wed, 23 Sep 2015 19:35:41 GMT

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?

Re: Missing age groups

Wed, 23 Sep 2015 19:36:53 GMT

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

Re: Missing age groups

pamaxeed — Wed, 23 Sep 2015 19:43:19 GMT

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

Re: Missing age groups

maxgro — Wed, 23 Sep 2015 19:47:07 GMT

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];

Re: Missing age groups

Digvijay_Singh — Wed, 23 Sep 2015 19:47:55 GMT

Good catch maxgro‌, as script is working fine with normal cases. He may need to round the ages to nearest integer.

Re: Missing age groups

Wed, 23 Sep 2015 20:09:32 GMT

Thanks Maxgro, I used the script:

round([Age (yrs)],1) as [CMED Age],

and it fixed the age groups for all the names.

Thanks everyone!!

Re: Missing age groups

sasiparupudi1 — Tue, 29 Sep 2015 05:53:21 GMT

Please close this thread by marking a correct answer so that it will be helpful for others who might be looking for a similar solution