Using age ranges for a listbox

[#1]

Hi People,

i have the following situation. In my primairy system or Excel I defined some ranges of age. (there i will validate that there is no overlap)

For example:

From       Till

0             18

19           25

26           65

66          130

In my Qlikview application i want a listbox containing the items above, so:

0 - 18

19 - 25

26 - 65

66 - 130

So I added a listbox and at the property Field i used a expression.

In my Qlikview application the connection with range of age is the birthdate. A while ago a had a solution that was proportional:

=Class(floor((Today()-BIRTHDATE)/365.25), varAgeRange, 'Leeftijd')

But now i want to use the ranges that can be setup by the user, anybody a idea how the expression will be?

thanks alot!

[swuehl]

Please check this thread on how to create an age classification (BTW, there is an age() function in QV, so no need to calculate age like above):

http://community.qlik.com/thread/4967

[#3]

correct me if i'm wrong but do I understand it right and you would solve this in the script.

So the first data you will load will contain the ranges:

0 - 18

19 - 25

26 - 65

66 - 130

the second table which contains the birthdate there you will make a extra field of Age? Of will you convert it there to the range?

So record:

Id     Name      birthdate          newField

1     Jans         01-01-1983      26-65      

?

I do not see how i can make a link between newField and the ranges of ages. Because the next thime the ranges can be:

0 - 20

21 - 30

31 - 70

71 - 90

91 - 140

And then it must still work without making any updates in the script

[swuehl]

Your script might look like this (just check also Robs post in referenced  thread):

Set DateFormat = 'DD-MM-YYYY'; // Set your date format to the format used below

AgeGroups:
MAPPING LOAD
Low+(iterNo()-1) as Key,
Group
WHILE iterNo() <= (High - Low) + 1
;
LOAD Low, High, Group FROM YourAgeGroupTable; // Load from DB or Excel

TestData:
LOAD *,
applyMap('AgeGroups', Age(today(), birthdate), 'Older than thought') as AgeGroup
;
LOAD * INLINE [

Id,     Name,      birthdate         

1,     Jans ,        01-01-1983     
]
; //Instead of this INLINE LOAD, LOAD from your data source

[#5]

I''m further now for that thanks a lot!

The only issue that now is left is that i want the ranges in a listbox and for now I choose Group , but then I only see the ranges where is data for. And if a range is not in the data you won't see him

Any idea how to solve this problem?

[#6]

Age_Map:

MAPPING LOAD High+(iterNo()-1) as Key,

Group

WHILE IterNo() <= (High - Low) + 1

;

LOAD Low, High, Group from ages.xlsx

(ooxml, embedded labels, table is Blad1);

The ages.xlsx

contains

Low	Tot	Group
5	18	0 - 18
19	25	19 - 25
26	65	26 - 65
66	120	66 - 120

I have the following script

people:

LOAD

          peopleId,

          BIRTHDATE,

          applyMap('Age_Map', Age(today(), BIRTHDATE), 'what to say here?') as AgeGroup;

SQL SELECT

    peopleId,

          BIRTHDATE,

FROM people;

this table contains the following records:

peopleId     BIRTHDATE     AgeGroup

1                    01-01-1988     19 - 25

2                    01-01-1970     26 - 65    

3                    01-01-1912     65-120

As you can see i have no data for the range : 0 - 18

The listbox in my Qlikview application must show all ranges: 0 - 18, 19 - 25, 26 - 65 and 66 - 120.

As it is working now i don't see range 0 - 18

[swuehl · Accepted solution]

Maybe like this:

Set DateFormat = 'DD-MM-YYYY'; // Set your date format to the format used below

AgeGroups:
LOAD
Low+(iterNo()-1) as Age,
AgeGroup
WHILE iterNo() <= (High - Low) + 1
;
LOAD Low, High, AgeGroup FROM YourAgeGroupTable; // Load from DB or Excel

TestData:
LOAD *

, Age(today(), birthdate) as Age

;
LOAD * INLINE [

Id,     Name,      birthdate         

1,     Jans ,        01-01-1983     
]
; //Instead of this INLINE LOAD, LOAD from your data source

i.e. don't use a mapping, but keep all age groups in your data model and link by age to your data.