11 Replies Latest reply: May 7, 2011 1:30 PM by Nagaian Krishnamoorthy

Split a table in intervals

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

• Split a table in intervals

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

• Split a table in intervals

Hi,

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

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

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

Table:
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;

• Split a table in intervals

Hi,

here I am attaching qvw file...

• Split a table in intervals

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.

• Split a table in intervals

Hi,

so this script may solve your problem....

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

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

Table:
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;

• Split a table in intervals

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?

• Split a table in intervals

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.

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

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

DROP TABLE Temp_Table;

• Split a table in intervals

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?

• Split a table in intervals

For the second part of your question you can change the last part of the script into:

Table:
RowNo() as Rank,
SumTotalamount
RESIDENT Temp_Table
Where SumTotalamount >'0'
ORDER BY SumTotalamount desc;

• Split a table in intervals

Take a look at the file, hope it helps.

• Re: Split a table in intervals

I hope you have already resolved your problem. If not, you may review the attached qvw file for a solution.