Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
So, 2days and 3 days will also go into Others?
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;