topic Split a table in intervals in QlikView

Split a table in intervals

Mon, 02 May 2011 10:07:52 GMT

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!

Split a table in intervals

Mon, 02 May 2011 11:20:44 GMT

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

Mon, 02 May 2011 11:34:41 GMT

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;

Split a table in intervals

Mon, 02 May 2011 11:58:30 GMT

Hi,

here I am attaching qvw file...

Split a table in intervals

Mon, 02 May 2011 12:34:13 GMT

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

Mon, 02 May 2011 13:11:33 GMT

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');

Split a table in intervals

Mon, 02 May 2011 14:23:58 GMT

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

Anonymous — Mon, 02 May 2011 14:39:41 GMT

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;

Split a table in intervals

Anonymous — Mon, 02 May 2011 15:02:39 GMT

Take a look at the file, hope it helps.

Split a table in intervals

Mon, 02 May 2011 15:12:01 GMT

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

Anonymous — Mon, 02 May 2011 17:09:55 GMT

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

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

Re: Split a table in intervals

nagaiank — Sat, 07 May 2011 17:30:47 GMT

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