topic Using age ranges for a listbox in QlikView

Using age ranges for a listbox

Tue, 04 Dec 2012 12:50:09 GMT

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!

Re: Using age ranges for a listbox

swuehl — Tue, 04 Dec 2012 13:05:36 GMT

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

Re: Using age ranges for a listbox

Tue, 04 Dec 2012 13:34:12 GMT

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

Re: Using age ranges for a listbox

swuehl — Tue, 04 Dec 2012 13:50:57 GMT

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

Re: Using age ranges for a listbox

Tue, 04 Dec 2012 14:12:05 GMT

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?

Re: Using age ranges for a listbox

Tue, 04 Dec 2012 14:37:46 GMT

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

Re: Using age ranges for a listbox

swuehl — Tue, 04 Dec 2012 14:40:19 GMT

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.