Split a table in intervals

[#1]

Hi

I need some help with splitting a table in intervals

I have a simple table that has two fields

customerid, totalamount

This table is changed from day to day.

What I want to do is to add a column to this table ranking customers from 1-5 depending on how much they've bought (totalamount). So i want to split the table into 5 "clusters" ranking from 5-1 where 5 is the customers who has bought the most.

This seems fairly simple but I can't figure it out

Thanks in advance!

[#2]

I forgot to add that these 5 clusters should be exactly the same size and I want to do all this in the load script

[#3]

Hi,

please go through this script...It may solve ur problem...

Data:
load
*
INLINE [
Customerid, Totalamount
1, 100
2, 130
3, 50
4, 80
5, 70
] ;

load
Customerid,
Totalamount
Resident Data Order by Totalamount;
Let vMax=Peek('Totalamount',-1,Data);

Table:
load
Customerid,
if(Totalamount>=$(vMax)/5*4,1,
if(Totalamount>=$(vMax)/5*3 and Totalamount<$(vMax)/5*4,2,
if(Totalamount>=$(vMax)/5*2 and Totalamount<$(vMax)/5*3,3,
if(Totalamount>=$(vMax)/5*1 and Totalamount<$(vMax)/5*2,4,5)))) as Rank
Resident Data;

[#4]

Hi,

here I am attaching qvw file...

[#5]

Thanks! This is somewhat what I'm looking for but not quite.

I don't really care about the totalamount variable.

I just want to count all customers and split them into 5 clusters where 5 = the ones with most totalamount but each cluster should contain exactly the same amount of customers.

[#6]

Hi,

so this script may solve your problem....

Data:
load
*
INLINE [
Customerid, Totalamount
1, 100
2, 130
3, 50
4, 80
5, 70
] ;

load count(Customerid) as count
Resident Data Group by Totalamount;
let vMax=Peek('count');

Table:
load
Customerid,
if(Totalamount>=$(vMax)/5*4,1,
if(Totalamount>=$(vMax)/5*3 and Totalamount<$(vMax)/5*4,2,
if(Totalamount>=$(vMax)/5*2 and Totalamount<$(vMax)/5*3,3,
if(Totalamount>=$(vMax)/5*1 and Totalamount<$(vMax)/5*2,4,5)))) as Rank
Resident Data;

[#7]

Hmm no this didn't work. I just got 1 on all values.

Isn't there an easy way to just make a counter in my customers table somehow?

[Anonymous]

Yes you are right, that doesn't work for what you want.
And you are right to first want to make a counter (rank) of your costumers.

you can do this by adding this to your script:

Temp_Table:
LOAD Customerid,
sum(Totalamount) as SumTotalamount
Resident Data
GROUP BY Customerid;

Table:
LOAD Customerid,
RowNo() as Rank,
SumTotalamount
RESIDENT Temp_Table
ORDER BY SumTotalamount asc; // or desc

DROP TABLE Temp_Table;

[Anonymous]

Take a look at the file, hope it helps.

[#10]

Cool, yeah I got this to work, but I need to dynamically sort them in groups since the number of customer is always increasing. I also need to ignore those customers who has sumtotalamount<=0. How can I do this simply?