Age Bucket

[#1]

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.

[Anonymous]

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.

[#3]

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.

[VishalWaghole]

Hi Aviamakky,

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

-- Regards,

Vishal

[mahesh_agrawal]

Hi,

Please find the attached QVW file.

[jonathandienst]

Hi

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

[#7 · Accepted solution]

Dear Aviamakky

I converted all into Days,

now you have to change according.

find attached file.

Thanks & Regards

Vimlesh Gupta