Skip to Content

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

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • May 17, 2017 at 12:44 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • May 17, 2017 at 01:54 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • May 18, 2017 at 02:35 AM

    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

    Add comment
    10|10000 characters needed characters exceeded