cancel
Showing results for
Did you mean:
Creator III

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

1 Solution

Accepted Solutions
Master III

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;

9 Replies
Master III

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

Master

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

Creator III
Author

Hi Antonio and good morning to you!

Creator III
Author

Hi Andrew and thnx!!,

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

Master

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

Creator III
Author

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.

Creator III
Author

Almost, Andrew..

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

Master III

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,
]
;