sum of sales

[#1]

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

[swuehl]

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.

[saimahasan]

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;