topic Re: Srcipt in QlikView

Srcipt

smilingjohn — Sun, 28 Feb 2016 04:35:42 GMT

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

Re: Srcipt

phaneendra_kunc — Sun, 28 Feb 2016 06:53:36 GMT

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);

Re: Srcipt

maxgro — Sun, 28 Feb 2016 08:43:26 GMT

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);

Re: Srcipt

MarcoWedel — Sun, 28 Feb 2016 21:56:32 GMT

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

Re: Srcipt

swuehl — Sun, 28 Feb 2016 22:20:43 GMT

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);

Re: Srcipt

MarcoWedel — Sun, 28 Feb 2016 22:28:01 GMT

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

Re: Srcipt

smilingjohn — Mon, 29 Feb 2016 04:32:43 GMT

Thanks Maxgro

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

Field must be unique within table .