Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi i have two tables
Table A | ||||
S.no | code | Doc.code | Total | |
1 | ILE | 1001 | 20 | |
2 | ILE | 1002 | 40 | |
3 | ILE | 1003 | 30 | |
4 | ILE | 1004 | 50 | |
5 | ILE | 1005 | 80 | |
Table B | ||||
s.no | code | Ref code | Ref Doc code | Total |
1 | SRLE | ILE | 1002 | 20 |
2 | SRLE | ILE | 1004 | 40 |
3 | SRLE | ILE | 1005 | 80 |
Given above are two tables. i want the sum of sales for ILE by subtracting the values from table B
ie. second table is my sales return and first table is invoice. if sales return hapens i need to return the money so it wont count as profit..
how to achieve this type..
please help
Thanks
Vignesh
One possible way would be to concatenate both tables, renaming Ref Doc code to Doc code and multiplying the Total with -1.
then you can aggregate Total grouped by Doc code and you should see the correct amount.
Do you want something like this:
Table1:
LOAD * Inline [
S.no, code, Doc.code, Total
1, ILE, 1001, 20
2, ILE, 1002, 40
3, ILE, 1003, 30
4, ILE, 1004, 50
5, ILE, 1005, 80
];
Join
LOAD * Inline [
s.no, S_code, code, Doc.code, S_Total
1, SRLE, ILE, 1002, 20
2, SRLE, ILE, 1004, 40
3, SRLE, ILE, 1005, 80
];
T2:
LOAD *,
Total - If(IsNull(S_Total),0,S_Total) as NewTotal
Resident Table1;
DROP Table Table1;