HANA Calculation View Join

[Former Member]

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

[former_member185132 · Accepted solution]

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

[Former Member · Accepted solution]

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   

[former_member185132 · Accepted solution]

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

[Former Member · Accepted solution]

Hi Suhas,

Thanks you for your time and support! Expectation is everything should come in single line. Looking for other alternatives. Thank you again.

Regards,

Kotesh  

[former_member185132 · Accepted solution]

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.

[Former Member · Accepted solution]

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  

[former_member185132 · Accepted solution]

Yes, I am aware that PO Items and Invoices do not have a 1:1 relation. That is exactly why I said that when you combine them as per your requirement, you do need to define a business logic to determine what is supposed to happen to the measures coming from the PO side of the coin.

[Former Member · Accepted solution]

Thanks all for your support. Going with different design solutions and hence closing.  

[Former Member]

Sorry guys.. Wrong PO number updated in Table A.

PO No is same in both the tables i.e PO No = 100.