Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody,
I don't know if it's possible to do what I want but, here it is:
What I have is:
key | client | amount |
---|---|---|
1 | A | 1000 |
1 | B | 1000 |
1 | C | 2000 |
2 | A | 1000 |
2 | D | 500 |
What I want is:
key | client | amount | amoun_prorated |
---|---|---|---|
1 | A | 1000 | amount1A + 0,5*amount1B + 0,5*amount1C = 2500 |
1 | B | 1000 | 0,5*amount1B = 500 |
1 | C | 2000 | 0,5*amount1C =1000 |
2 | A | 1000 | amount2A + 0,5*amount2D = 1250 |
2 | D | 500 | 0,5*amount2D = 250 |
So, I explain myself:
I don't know if it's clear of not...
Thanks in advance.
Andoni
Try this
Table:
LOAD key,
client,
amount,
If(client = 'A', amount, amount/2) as temp_amount;
LOAD * INLINE [
key, client, amount
1, A, 1000
1, B, 1000
1, C, 2000
2, A, 1000
2, D, 500
];
Left Join (Table)
LOAD key,
'A' as client,
Sum(temp_amount) as NewAmount
Resident Table
Where client <> 'A'
Group By key;
FinalTable:
LOAD key,
client,
amount,
RangeSum(temp_amount, NewAmount) as amount_prorated
Resident Table;
DROP Table Table;
Hi Sunny,
I think I forgot something. If there's no A client mixed with others with the same Key, amount_prorated should be 100% of each client. I give an example modifying the data used above (new data in bold):
key | client | amount |
---|---|---|
1 | A | 1000 |
1 | B | 1000 |
1 | C | 2000 |
2 | A | 1000 |
2 | D | 500 |
3 | B | 500 |
3 | F | 1000 |
key | client | amount | amoun_prorated |
---|---|---|---|
1 | A | 1000 | amount1A + 0,5*amount1B + 0,5*amount1C = 2500 |
1 | B | 1000 | 0,5*amount1B = 500 |
1 | C | 2000 | 0,5*amount1C =1000 |
2 | A | 1000 | amount2A + 0,5*amount2D = 1250 |
2 | D | 500 | 0,5*amount2D = 250 |
3 | B | 500 | = amount3B = 500 |
3 | F | 1000 | = amount3F = 1000 |
Sorry for the inconvenience and thanks for your answer.
Andoni
Try this
Table:
LOAD key,
client,
amount;
LOAD * INLINE [
key, client, amount
1, A, 1000
1, B, 1000
1, C, 2000
2, A, 1000
2, D, 500
3, B, 500
3, F, 1000
];
Left Join (Table)
LOAD key,
Sum(amount/2) as NewAmount1
Resident Table
Where client <> 'A'
Group By key;
Left Join (Table)
LOAD key,
Sum(amount/2) as NewAmount2
Resident Table
Where client = 'A'
Group By key;
FinalTable:
LOAD key,
client,
amount,
If(client = 'A', RangeSum(amount, NewAmount1), If(IsNull(NewAmount2), amount, amount/2)) as amount_prorated
Resident Table;
DROP Table Table;