How to pass the record values into another Bex query for match off

[Former Member]

Hello all,

I have a unique requirement and not sure how to achieve this.

Following are the columns currently in the transaction data:

Entity Trading partner INTCO Account Balances

E01276 I_00248 INTC011 1050$

E00248 I_01276 INTC012 -1050$

As you can see 1276 posted value to 248 and 248 posted back to 1276.

I need to display both the rows beside each other so that I can show the variance

final Output:

Entity TP INTCO Acc. Recv Bal. TP Entity INTCO Acc. Payable Bal. Variance

E01276 I_00248 INTC011 1050$ E00248 I_01276 INTC012 - 1050$ 0

I was hoping to standup 2 Bex query results displayed beside each other in BOBJ AO. Based on the results of the first query, for each row, it will take the entity value say E01276 ;remove E and replace with I_ and then pass this I_01276 into Trading partner of second query so that it can show in same row.

Is this feasible.

are there any options to achieve this scenario in backend or frontend.

[former_member269156]

Hi,

If you can differentiate by Accounts then you can create 2 Qs, as follows

Entity Trading partner INTCO Account Balances

E01276 I_00248 INTC011 1050$

E00248 I_01276 INTC012 -1050$

Q 1 : For account INTC011 and similar accounts

Q 2 : For account INTCO12 and similar accounts

Try to get mapping of records from Q2 based on Entity (by replacing I_with E) using VLOOKUP function as explained in

https://www.youtube.com/watch?v=qxjIx_Rp128

Note : In general Inter company postings will be under the same account for both sides ( Debit in one side and Cr in another side)...proposing this work around since you have mentioned 2 different accounts in the example .

If it is same account then create 2 reports by conditions

Q1 Amount > 0

Q2 Amount <0

Use VLOOKUP to get them into same line.

Hope it works.

Thanks,

Raju

[Former Member]

thank you for the answer.

My BEx query is built on Composite provider and there is no transformation that can happen at that layer.

So how will I replace the prefix I_ with E and pass that value to Entity?

Also how will left out join on same DSO help for this scenario as it will lead to no data.

[kohesco]

Hi,

I don't have the details of your flow, but I still advise you to make the changes on that level.

a left outer should do the trick

• I_00248-> 00248
• E00248-> 00248

check out

https://blogs.sap.com/2015/11/07/hana-composite-provider-hcpr-practical-tips/

for modeling tips

[kohesco]

Hi,

can't you resolve it in the back-end? creating a left outer join on the same table (create a duplicate view) create new objects in those views, so you can define keys to use for the left outer join entity/trading partner. Better to create a good dataset than create something in the front-end I guess

Grtz

Koen