Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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;
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
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
Hi Antonio and good morning to you!
Thnx for your swift reply - Gonna try that straight away!
Hi Andrew and thnx!!,
Yes I need it done in the load script...
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
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.
Almost, Andrew..
the 5 mln @ 51118 needs to be 2.5 mln, as there are 2 records
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;
Spot on, Antonio, thnx!!