Discussion Board for collaboration related to Creating Analytics for QlikView.
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)
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?
Go to 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, AgeGroupWHILE iterNo() <= (High - Low) + 1;LOAD Low, High, AgeGroup FROM YourAgeGroupTable; // Load from DB or Excel
, 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.
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):
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:
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?
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
Your script might look like this (just check also Robs post in referenced thread):
AgeGroups:MAPPING LOAD Low+(iterNo()-1) as Key, GroupWHILE 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 [
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?
MAPPING LOAD High+(iterNo()-1) as Key,
WHILE IterNo() <= (High - Low) + 1
LOAD Low, High, Group from ages.xlsx
(ooxml, embedded labels, table is Blad1);
I have the following script
applyMap('Age_Map', Age(today(), BIRTHDATE), 'what to say here?') as AgeGroup;
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