SPLIT RECORDS IN SETS HAVING THE SAME sum(Value)

[#1]

Hi all,

Let's assume I have a table  with a simple structure:

RecordID	Value
1	10
2	20
3	25
4	15
5	100
...	...
n	1000

I would like to ORDER the records by Value (ascending) and then SPLIT the records in 10 sets, each set HAVING THE SAME sum(Value) - that is sum(TOTAL Value)/10.

The end result would be a chart showing all those 10 sets and the number of records contained in each set. Something like this:

Records Set	#Records
Set1	50
Set2	30
Set3	15
...	...
Set10	2

Could anyone please suggest a solution for this?

Thanks,
Carmen

[Anonymous]

Hi Carmen,

I am not understanding how you want to split in 10 sets.

I am thinking that you would first

Load your initial table T1,

Reload it using a resident load order by value asc;

Then flag similar record to create your 10 theoretical subsets.

Kind regards,

Antoine

[stigchel]

I think you can use the Aggr(rank(Expression),RecordId) function  and use the rank as dimension with a count of RecordID...

[CELAMBARASAN]

Hope the attachment might helps.

I have used inline load. You can generate this using rowno or recno.

[Gysbert_Wassenaar]

See attached example.