how to create buckets if we have combination of text and number

[grajmca_sgp123]

hi,

I have a requirement to create buckets based text field which will have combination of integer and text.

For example ,below is the sample input data 

Input data field:

Data

2days

3days

7days

15days

30days

1month

2month

4month

8month

12month

ABC

ABCD

123ABC

expected output:

Bucket	Period
7days-1month	1Month
2month-4month	3month
5month-7month	6month
Any other value	Others

Can any help me on it.

[sunny_talwar]

So, 2days and 3 days will also go into Others?

[Kushal_Chawda]

Assuming that 2Months-4Months includes 2Months, 3Months & 4Months. If not then Adjust the From and To values in days. For months put it in multiple of 30 as calculation will be on days (for eg. 2 months -2*30)

Data: 
load *, 
if(Index(lower(trim(Data)),'day'),keepchar(Data,'0123456789'),
if(Index(lower(trim(Data)),'month'),keepchar(Data,'0123456789')*30,0)) as NumData 
Inline [
Data
2days
3days
7days
15days
30days
1month
2month
4month
8month
12month
ABC
ABCD
123ABC
];

// Numbers in From & To are in days. For months put values multiple of 30
Bucket:
Load * Inline [
From,To,Bucket
7,30,7Days-1Month
31,120,2Months - 4Months
121,210,5Months - 7Months
0,0,Others ];

Inner Join
IntervalMatch(NumData)
Load From,To
Resident Bucket;

Left Join(Data)
Load NumData,
     Bucket
Resident Bucket;

Drop Table Bucket;

Final:
NoConcatenate
Load Data,
     NumData,
     if(len(trim(Bucket))=0,'Others',Bucket) as Bucket
Resident Data;

Drop Table Data;