topic Re: creating an age group from a date field in QlikView

creating an age group from a date field

Wed, 11 Nov 2009 19:13:58 GMT

Hi,

I have an Age field and am trying to create an Age Group field based around it.

The Age group is

under 16

16-35

36 - 55

56 - 65

Over 65

creating an age group from a date field

Wed, 11 Nov 2009 19:42:09 GMT

Its best to create the group in the load script and then use this new field called Age_Group.
Try the below example

LOAD
if(age > 65, 'Over 65',
if(age <= 65 and age >= 56, '56 - 65',
if(age <= 55 and age >= 36, '36 - 55',
if(age <= 35 and age >= 16, '16 - 35',
if(age < 16, 'Under 16'))))) as Age_Group;
SQL Select *
FROM Table1;

creating an age group from a date field

johnw — Thu, 12 Nov 2009 06:24:22 GMT

That's pretty much what I'd do, but I'd take advantage of knowing we failed the previous IFs to simplify the expression a bit for performance:

if(age > 65, 'Over 65',
if(age >= 56, '56 - 65',
if(age >= 36, '36 - 55',
if(age >= 16, '16 - 35',
'Under 16')))) as Age_Group

If performance is an issue, you might also want to see if a mapping table is faster. I think it would look like this:

AgeGroups:
MAPPING LOAD
Age
,if(Age > 65, 'Over 65',
,if(Age >= 56, '56 - 65',
,if(Age >= 36, '36 - 55',
,if(Age >= 16, '16 - 35',
'Under 16')))) as AgeGroup
;
LOAD recno() as Age
AUTOGENERATE 200
;
LOAD *
,applymap('AgeGroups',Age) as AgeGroup
;
SQL SELECT *
FROM Table1
;

The idea is that you save time by only performing the IF once per age, not once per row. Or for that matter, just leave AgeGroups as a separate table linked to your main table by Age. That will probably load fastest, but may be slightly slower to display in charts. Just guessing, though.

creating an age group from a date field

Anonymous — Fri, 11 Dec 2009 04:31:47 GMT

I'm trying to implement your previous suggestion for my numeric field called Age which is located in a table I call Data. (The Data table I created from a .txt file.) I get the following error message:

ODBC connection failed
SQL Select *
FROM Data

I'm guessing that I can't use the code SQL Select * but I'm not sure what to substitute.

Any help would be appreciated!

creating an age group from a date field

johnw — Fri, 11 Dec 2009 06:56:02 GMT

The example I gave was merely an example. If you're loading from a text file instead of from an ODBC data source that uses SQL, you don't want to use an SQL select. You want to load from your text file the way you normally do.

AgeGroups:
MAPPING LOAD
Age
,if(Age > 65, 'Over 65',
,if(Age >= 56, '56 - 65',
,if(Age >= 36, '36 - 55',
,if(Age >= 16, '16 - 35',
'Under 16')))) as AgeGroup
;
LOAD recno() as Age
AUTOGENERATE 200
;
[Your table name goes here]:
LOAD *
,applymap('AgeGroups',Age) as AgeGroup
;
The same script you've always used to load the fields from your text file goes here.

creating an age group from a date field

rwunderlich — Fri, 11 Dec 2009 13:16:44 GMT

Here's an alternative way to create the mapping table that avoids the Ifs (and paren-disease).

AgeGroups:
MAPPING LOAD
Low+(iterNo()-1) as Key,
Group
WHILE iterNo() <= (High - Low) + 1
;
LOAD * INLINE [
Low, High, Group
0,15,Under 16
16,35,16-35
36,55,36-55
56,65,56-65
]
;

TestData:
LOAD *,
applyMap('AgeGroups', Age, 'Over 65') as AgeGroup
;
LOAD * INLINE [
Age
1
3
15
16
30
55
99
]
;

Not a huge improvement, but may make it easier to maintain the range table.

-Rob

Re: creating an age group from a date field

rbecher — Fri, 20 Jul 2012 21:59:08 GMT

Yet another calculated solution:

if(Age<16, 'Under 16',

if(Age>65, 'Over 65',

text(16+(Div(Age-16,20)*20)) & ' - ' & text(35+(Div(Age-16,20)*20)))) as AgeGroup

But you always have to deal with the fringe groups..

- Ralf

Re: creating an age group from a date field

whiteline — Mon, 05 Nov 2012 09:12:37 GMT

The final touch is to add dual() function so that the groups can be sorted properly.

I think the fringe groups is not a drawback as it's often perceived by users this way even if it's not.

Re: creating an age group from a date field

berndjaegle — Wed, 11 Apr 2018 12:24:52 GMT

Thanx Rob, it is a big improvement for me. I have to handle 30+millions of records. Doing it with map instead of IF is faster.

Re: creating an age group from a date field

surya_30 — Wed, 20 Apr 2022 13:14:16 GMT

Hi, I was wondering what's the best way to group customers by the Asset Value without using if condition

Cust ID Asset Value Segment

1 < 1million

1 1-2 million

If asset value is coming from another table other than the customer and it is split across months so need needs to be summed up