Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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;
Hi,
here I am attaching qvw file...
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.
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;
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?
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;
Take a look at the file, hope it helps.
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?