Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have the data like below table 1, and I want to group it like table 2, Now I'm using a if to do it, does anybody have experience using class function to archive the same result?
Table1
Table2:
Your intervals are not equal so class will no w0rk here...
Better you create the bucket in the load script.
Regards
Anand
If you don't need these clustering dynamically use the suggestion from Anand Chouhan maybe with class or a mapping-approach. Should it be dynamic you could use an if-loop inside the class-function to create non-equeal buckets (you have only to avoid an overlapping from the buckets) but better would be you used equal bucket-sizes controlled by a variable - so the user could choose which buckets he/she want to see.
- Marcus
demo:
LOAD ID,
date,
Sales,
Month(date) as mon,
Day(date) as day,
Year(date) as year
FROM
(ooxml, embedded labels, table is Sheet1);
new:
LOAD *,
if(match(day>=1,day<=7),'Bucket1',
if(match(day>=8, day<=14),'Bucket2',
if(match(day>=15, day<=21),'Bucket3',
if(match(day>=22 , day<=28),'Bucket4',
if(match(day>=29 , day<=31),'Bucket5'))))) as Bucket
Resident demo;
DROP Table demo;
//new:
//LOAD *,
//
//if(Match(day,1,2,3,4,5,6,7) , 'bucket1',
//if(Match(day,8,9,10,11,12,13,14), 'bucket2',
//if(Match(day,15,16,17,18,19,20,21), 'bucket3',
//if(Match(day,22,23,24,25,26,27,28), 'bucket4',
//if(Match(day,29,30,31), 'bucket5'))))) as bucket
//Resident table;
//DROP Table table;
//
If you have such consistent buckets you could it make easier with something like:
...
'Bucket' & ceil(day / 7) as bucket
...
and this already within the first load: ceil(day(date) / 7).
- Marcus