Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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:
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
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
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?
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
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.