Srcipt

[smilingjohn]

HI ALl ,

I want to show the age in category ,Iam have convereted Date of birth date filed into AGE , and at the script level I am also trying to convert date into cateodry

like this

if(age(now(),BIRTHDATEODBC)>11 and age(now(),BIRTHDATEODBC)<=19,'Teenager',

              if(age(now(),BIRTHDATEODBC)>19 and age(now(),BIRTHDATEODBC)<=30,'Young Aged',

               if(age(now(),BIRTHDATEODBC)>30 and age(now(),BIRTHDATEODBC)<=55,'Middle Aged',

               if(age(now(),BIRTHDATEODBC)>55 ,'Old Aged')))))As Agebucket

But its not working

Please fine the QVW application of sample data

[phaneendra_kunc]

Try this code.

Load *, if(Age<=11,'Child',

              if(Age>11 and Age<=19,'Teenager',

              if(Age>19 and Age<=30,'Young Aged',

               if(Age>30 and Age<=55,'Middle Aged',

               if(Age>55 ,'Old Aged')))))As Agebucket;

LOAD BIRTHDATEODBC,

Age(Date(Today()), Date(Date#(BIRTHDATEODBC,'M/D/YYYY  hh:mm:ssTT'))) as Age

FROM

(ooxml, embedded labels, table is Sheet1);

[maxgro]

you can use a preceding load to calculate the Agebucket

LOAD

  BIRTHDATEODBC,

  Age,

  if(Age<=11, 'Child',

  if(Age<=19, 'Teenager',

  if(Age<=30, 'Young Aged',

  if(Age<=55, 'Middle Aged',

  'Old Aged'

  )))) as Agebucket

  ;

LOAD BIRTHDATEODBC,

Age(Date(Today()), Date(Date#(BIRTHDATEODBC,'M/D/YYYY  hh:mm:ssTT'))) as Age

FROM

(ooxml, embedded labels, table is Sheet1);

[MarcoWedel · Accepted solution]

Hi,

another one to also convert the birth date itself to a proper date value:

LOAD *,
    If(Age<=11,'Child',
     If(Age>11 and Age<=19,'Teenager',
      If(Age>19 and Age<=30,'Young Aged',
       If(Age>30 and Age<=55,'Middle Aged',
        If(Age>55 ,'Old Aged'))))) as Agebucket;
LOAD *,
    Age(Today(), BIRTHDATEODBC) as Age;
LOAD Date(Date#(BIRTHDATEODBC,'M/D/YYYY  hh:mm:ssTT')) as BIRTHDATEODBC
FROM [Age.xlsx] (ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco

[swuehl]

Another option for the age bucket creation:

MAPPING
LOAD FROM + IterNo()-1  as AGE, CLASS
INLINE [
FROM, TO, CLASS
0,11, Child
12,19, Teenager
20,30,Young Aged
31,55, Middle Aged
56,150, Old Aged
]
WHILE FROM + IterNo()-1 <= TO;

LOAD *,
ApplyMap('AGEMAP',Age,'No mapping found') as Agebucket;
LOAD *, 
Age(Today(), BIRTHDATEODBC) as Age; 
LOAD Date(Date#(BIRTHDATEODBC,'M/D/YYYY hh:mm:ssTT')) as BIRTHDATEODBC 
FROM [Age.xlsx] (ooxml, embedded labels, table is Sheet1); 

[MarcoWedel]

Hi,

if you want to load the buckets from a table instead of hard coding, and in order to be able to sort the buckets by load order, another solution might be:

tabAgebuckets:
LOAD AgeFrom+IterNo()-1 as Age,
    Agebucket
While AgeFrom+IterNo()-1 <= AgeTo;
LOAD Alt(Previous(AgeLimit)+1,0) as AgeFrom,
    AgeLimit as AgeTo,
    Agebucket
Inline [
AgeLimit, Agebucket
12,Child
19,Teenager
30,Young Aged
55,Middle Aged
130,Old Aged
];

mapAgebuckets:
Mapping LOAD * Resident tabAgebuckets;

tabAges:
LOAD *,
    ApplyMap('mapAgebuckets',Age) as Agebucket;
LOAD *,
    Age(Today(), BIRTHDATEODBC) as Age;
LOAD Date(Date#(BIRTHDATEODBC,'M/D/YYYY  hh:mm:ssTT')) as BIRTHDATEODBC
FROM [Age.xlsx] (ooxml, embedded labels, table is Sheet1);

DROP Table tabAgebuckets;

hope this helps

regards

Marco

[smilingjohn]

Thanks Maxgro

When i tried to leaod you r script ther is an error that is

Field must be unique within table .