Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Ones, I turn to you again for help. I have a sales table, associated with a code and a referral. For example the code 20 was referred by code 300:
Codigo | Ref | Sum(Venta) | |
18.272.049 | |||
| 300 | 2.167.167 | |
5 | 500 | 1.334.083 | |
150 | 900 | 4.594.428 | |
1 | 1.035.606 | ||
9 | 1.174.284 | ||
500 | 1.977.784 | ||
900 | 2.971.565 | ||
300 | 3.017.132 |
So if I want to see the contribution of code 300 I should consider besides the sale of code 20.
Codigo | Sum(Monto) | Monto Ref | A+B |
18.272.049 | |||
20 | 2.167.167 | 2.167.167 | |
5 | 1.334.083 | 1.334.083 | |
150 | 4.594.428 | 4.594.428 | |
1 | 1.035.606 | 1.035.606 | |
9 | 1.174.284 | 1.174.284 | |
500 | 1.977.784 | 1.334.083 | 3.311.867 |
900 | 2.971.565 | 4.594.428 | 7.565.993 |
300 | 3.017.132 | 2.167.167 | 5.184.299 |
I hope you can help me.
Hi
Assuming that the Sum(Venta) is a number but in a different format
Master: LOAD Codigo, Ref, [Sum(Venta)] FROM [.\Comm1521800.xlsx] (ooxml, embedded labels, table is Sheet1); Left join (Master) Load Ref as Codigo, [Sum(Venta)] as [Monto Ref] resident Master where IsNull(Ref)=0; MasterFinal: Load *, [Sum(Venta)]+[Monto Ref] as [A+B] resident Master; drop table Master;
Output
Codigo | Ref | Sum(Venta) | Monto Ref | A+B |
1 | 1035.606 | |||
5 | 500 | 1334.083 | ||
9 | 1174.284 | |||
20 | 300 | 2167.167 | ||
150 | 900 | 4594.428 | ||
300 | 3017.132 | 2167.167 | 5184.299 | |
500 | 1977.784 | 1334.083 | 3311.867 | |
900 | 2971.565 | 4594.428 | 7565.993 | |
18272.049 |