use class function to group customer spending

[Anonymous]

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:

[robert_mika]

Your intervals are not equal so class will no w0rk here...

[its_anandrjs]

Better you create the bucket in the load script.

Regards

Anand

[marcus_sommer]

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

[sumitjadhav]

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;

//

[marcus_sommer]

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