topic Re: Age Bucket in QlikView

Age Bucket

Tue, 15 Jul 2014 08:34:46 GMT

Hi All,

I am currently trying to create an Age Bucket for my application, however as the data captured in the Age column is in the following format I am not able to create the bucket:

Age

1 Days 3 Hrs

1 Days 18 Hrs

1 Mth 10 Days

1 Mth 11 Days

1 Mth 12 Days

1 Yrs 9 Mth

1 Yrs 10 Days

and so on.

The bucket we are trying to built is "<=15yrs", ">15 but <=35" and so on.

Need your assistance. I am attaching the excel with few records in it.

Re: Age Bucket

Anonymous — Tue, 15 Jul 2014 08:45:31 GMT

Hi,

Can you provide all bucket name here. You may either need to convert all age in days format and create bucket or create inline table with Age and Bucket and map to original table.

Re: Age Bucket

Tue, 15 Jul 2014 09:13:59 GMT

Hi Satyadev,

Following are the buckets we are trying to create:

>=25 yrs

<25 yrs and >=50 yrs

<50 yrs and >=75 yrs

75 yrs and above

Request you to help us with the code to convert the data into bucket.

Re: Age Bucket

VishalWaghole — Tue, 15 Jul 2014 09:23:59 GMT

Hi Aviamakky,

If their is BirthDate Column then directly use that column to calculate AgeBucket.

-- Regards,

Vishal

Re: Age Bucket

mahesh_agrawal — Tue, 15 Jul 2014 09:41:33 GMT

Hi,

Please find the attached QVW file.

Re: Age Bucket

jonathandienst — Tue, 15 Jul 2014 09:46:22 GMT

This load statement should do it:

Age:

LOAD Age,

AgeYears,

If(AgeYears <= 25, Dual('<=25', 25),

If(AgeYears <= 50, Dual('>25<=50', 50),

If(AgeYears <= 75, Dual('>50<=75', 75),

Dual('75+', 75)))) As Bucket

;

LOAD Age,

Age1 + Age2 As AgeYears

;

LOAD *,

F1Value * Pick(Match(F1Dim, 'Hrs', 'Days', 'Mth', 'Yrs'), 1/(365.25*24), 1/365.25, 1/12, 1) As Age1,

F2Value * Pick(Match(F2Dim, 'Hrs', 'Days', 'Mth', 'Yrs'), 1/(365.25*24), 1/365.25, 1/12, 1) As Age2,

;

LOAD Age

SubField(Age, ' ', 1) As F1Value,

SubField(Age, ' ', 2) As F1Dim,

SubField(Age, ' ', 3) As F2Value,

SubField(Age, ' ', 4) As F2Dim,

From Age_Sample.xls (biff, embedded labels, table is [Sheet1$]);

HTH

Jonathan

Re: Age Bucket

Tue, 15 Jul 2014 10:40:06 GMT

Dear Aviamakky

I converted all into Days,

now you have to change according.

find attached file.

Thanks & Regards

Vimlesh Gupta