9 Replies Latest reply: Aug 23, 2017 4:02 AM by Hans de Vries

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

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!!

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

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,
]
;
Resident Temp Group By Number;
If(A=1,Total-2500000*Count,Total) as NewTotal
Resident Temp;
Drop Table
Temp;

Regards,

Antonio

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

Hi Antonio and good morning to you!

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

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.

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

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,
]
;
Resident Temp Group By Number;
If(A=1,Total-2500000*Count,Total) as NewTotal
Resident Temp;
Drop Table
Temp;

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

Spot on, Antonio, thnx!!

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

Hi Hans,

Try this straight table:

Number sum(Total) Rangesum(sum(Total), - if(count(Number) >1, count(Number) *2500000))
38999100000002500000
4288925000002500000
4622325000002500000
5111850000000
76425100000002500000

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

cheers

Andrew

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

Hi Andrew and thnx!!,

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

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

Hi Hans,

Maybe this:

Data:

RowNo() as Row,

*;

Number, Total

76425, 10000000

76425

76425

51118, 5000000

51118

42889, 2500000

46223, 2500000

38999, 10000000

38999

38999,

];

MapDeduction:

Mapping

Number,

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

Resident Data Group by Number;

Left Join (Data)

Distinct

Number,

Total,

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

Resident Data;

Giving this tablebox:

Row Number Total NewTotal
176425100000002500000
276425
376425
45111850000000
551118
64288925000002500000
74622325000002500000
838999100000002500000
938999
1038999

cheers

Andrew

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

Almost, Andrew..

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