topic Re: use class function to group customer spending in QlikView

use class function to group customer spending

Anonymous — Fri, 20 Mar 2015 23:50:25 GMT

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:

Re: use class function to group customer spending

robert_mika — Sat, 21 Mar 2015 00:33:15 GMT

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

Re: use class function to group customer spending

its_anandrjs — Sat, 21 Mar 2015 05:24:10 GMT

Better you create the bucket in the load script.

Regards

Anand

Re: use class function to group customer spending

marcus_sommer — Sat, 21 Mar 2015 10:40:35 GMT

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

Re: use class function to group customer spending

sumitjadhav — Tue, 20 Oct 2015 08:41:02 GMT

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;

Re: use class function to group customer spending

marcus_sommer — Tue, 20 Oct 2015 08:51:58 GMT

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