on 12-11-2015 4:00 PM
Hi All,
I am looking for help on below issue:
I have requirement join below 2 tables to combine sales and invoice data. Joined in Calculation view using left outer join where
PO No of Table A = Po no of Table B and
PO Line of Table A = Po Line of Table B
Table-A:
PO No | Po Line | Product | Price | Qty |
4500360244 | 10 | Article1 | 100 | 10 |
4500360244 | 20 | Article2 | 200 | 5 |
Table-B:
PO No | Po Line | Invoice # | Invoice Amt | Currency |
100 | 10 | 11 | 400 | USD |
100 | 10 | 12 | 600 | USD |
100 | 20 | 13 | 1000 | USD |
First PO cleared with 2 invoices (11&12) and expecting below output
PO No | Po Line | Invoice # | Price | Qty | Invoice Amt | Currency |
100 | 10 | 11 | 100 | 10 | 400 | USD |
100 | 10 | 12 | 0 | 0 | 600 | USD |
100 | 20 | 13 | 200 | 5 | 1000 | USD |
But PO Price and Qty values are getting doubled and below is the output it is giving .
PO No | Po Line | Invoice # | Price | Qty | Invoice Amt | Currency |
100 | 10 | 11 | 100 | 10 | 400 | USD |
100 | 10 | 12 | 100 | 10 | 600 | USD |
100 | 20 | 13 | 200 | 5 | 1000 | USD |
Any early help is highly appreciated.
Regards,
Kotesh
Hi,
The only differences between the "expected" and "actual" outputs are the Price and Qty fields in the second line.
The "actual" output is exactly what you will get if you join the two tables that you have. Why do you expect these to be zero?
Regards,
Suhas
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Suhas,
Yes you are right Price and Quantity values are the differences. The problem here is it is calculating price as 200 & Quantity as 20 with the joining of above 2 tables instead of 100 & 10 respectively for the PO=100 & Po Line=10.
I am looking for other alternatives. I believe this is happening because of 2 invoice lines for the same key in the second table. Not sure how join will behave in this case.
Regards,
Kotesh
Hi,
I believe this is happening because of 2 invoice lines for the same key in the second table
That is precisely why this is happening. The join here will behave exactly you described above. The first line in Table A is related to two lines in Table B, which is why the measures coming out of A are getting doubled.
Now if you want to join the two tables and still want the measures to be populated only for one of the records in the result, then that's not possible with a join. A union of the two tables might be a better choice, but then the price coming from table A will not be shown alongside the Invoice # from table B. Please check if that is acceptable.
Regards,
Suhas
I doubt you will get an answer to that, because what you're asking for runs against both join and union concepts.
More than a limitation of the toolset here, this seems to be an incompleteness in the requirement. Before looking into the how, I'd suggest to analyse what is to be achieved here. Specifically, of the two invoices belonging to the PO, why did you assign the $100 to the line belonging to Invoice #11 and not to Invoice #12? What business logic determines this?
Also, when you assign the entire $100 to only one invoice, you are automatically implying that the PO Item price applies to only that invoice - which is a rather misleading thing to imply. Again, this is something you might want to take back to your customer.
Suhas here I gave just example amounts for the discussion shake. But in the real business scenario there are the cases where individual PO line item cleared with more than one invoice. I mean PO line item and Invoice document is not one to one relation always.
In BW PO data and Invoice data storing in different DSO's and facing this issue when we try to combine both the data sets.
Regards,
Kotesh
Sorry guys.. Wrong PO number updated in Table A.
PO No is same in both the tables i.e PO No = 100.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
95 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.