How do I count the # of records within a group?

[hansdevr]

Morning everyone!

Please take a look at this table:

Number	Total
76425	10000000
76425	--
76425	--
51118	5000000
51118	--
42889	2500000
46223	2500000
38999	10000000
38999	--
38999	--

It's a bit of a strange table: some numbers have a total, some don't (NULL). Now, what I want is to substract a certain amount from the total in the first record (which has a total) DEPENDING on the number of records within the Group (Group by Number).

If there are 3 records, I need to substract 7,500,000, when there are 2 I need to substract 5,000,000 and when there is 1 record, I don't need to do anything.

How is this accomplished?

Any help is greatly appreciated!!

[antoniotiman]

Hi Hans,

maybe like this

Temp:
LOAD *,AutoNumber(RowNo(),Number) as A Inline [
Number,Total
76425, 10000000
76425,
76425,
51118, 5000000
51118, 
42889, 2500000
46223, 2500000
38999, 10000000
38999, 
38999,
];
Join LOAD Number,Count(Number) as Count
Resident Temp Group By Number;
LOAD Number,Total,RowNo() as ID,
If(A=1,Total-2500000*Count,Total) as NewTotal
Resident Temp;
Drop Table Temp;

Regards,

Antonio

[effinty2112]

Hi Hans,

Try this straight table:

Number	sum(Total)	Rangesum(sum(Total), - if(count(Number) >1, count(Number) *2500000))
38999	10000000	2500000
42889	2500000	2500000
46223	2500000	2500000
51118	5000000	0
76425	10000000	2500000

Let me know if you need it done in the script instead.

cheers

Andrew

[hansdevr]

Hi Antonio and good morning to you!

Thnx for your swift reply - Gonna try that straight away!

[hansdevr]

Hi Andrew and thnx!!,

Yes I need it done in the load script...

[effinty2112]

Hi Hans,

Maybe this:

Data:

LOAD

RowNo() as Row,

*;

LOAD * INLINE [

    Number, Total

    76425, 10000000

    76425

    76425

    51118, 5000000

    51118

    42889, 2500000

    46223, 2500000

    38999, 10000000

    38999

    38999,

];

MapDeduction:

Mapping

LOAD

Number,

if(count(Number)>1,count(Number)*2500000,0)

Resident Data Group by Number;

Left Join (Data)

LOAD

Distinct

Number,

Total,

if(IsNum(Total),Total - ApplyMap('MapDeduction',Number)) as NewTotal

Resident Data;

Giving this tablebox:

Row	Number	Total	NewTotal
1	76425	10000000	2500000
2	76425
3	76425
4	51118	5000000	0
5	51118
6	42889	2500000	2500000
7	46223	2500000	2500000
8	38999	10000000	2500000
9	38999
10	38999

cheers

Andrew

[hansdevr]

No, that's not it I'm afraid..

If there's only one number - total remains unchanged;

If there are two equal numbers - total needs to be substracted with 2.5 mln;

if there are three equal numbers - total needs to be substracted with 5 mln.

[hansdevr]

Almost, Andrew..

the 5 mln @ 51118 needs to be 2.5 mln, as there are 2 records

[antoniotiman · Accepted solution]

Temp:
LOAD *,AutoNumber(RowNo(),Number) as A Inline [
Number,Total
76425, 10000000
76425,
76425,
51118, 5000000
51118, 
42889, 2500000
46223, 2500000
38999, 10000000
38999, 
38999,
];
Join LOAD Number,Count(Number)-1 as Count
Resident Temp Group By Number;
LOAD Number,Total,RowNo() as ID,
If(A=1,Total-2500000*Count,Total) as NewTotal
Resident Temp;
Drop Table Temp;

[hansdevr]

Spot on, Antonio, thnx!!