6 Replies Latest reply: Dec 4, 2012 9:50 AM by Stefan Wühl RSS

    Using age ranges for a listbox

      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
          Stefan Wühl

          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

              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
                  Stefan Wühl

                  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

                      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
                          Stefan Wühl

                          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.

                  • Re: Using age ranges for a listbox

                    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

                     

                    LowTotGroup
                    5180 - 18
                    192519 - 25
                    266526 - 65
                    6612066 - 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