Hi @thomas.trill  - unfortunately, it is not possible to write a subquery in this manner directly in an OLAP table for non-eventlog tables. That said, the following approach would absolutely work:

• In event collection, create a two-column table containing the KONNR and EKGRP fields using the following query:
  • SELECT DISTINCT KONNR, EKGRP FROM EKKO AS E1 INNER JOIN EKKO E2 ON 1=1 AND E1.KONNR = E2.EBELN;
• Add this table to the data model by joining it to EKKO on NEW_TABLE.KONNR = EKKO.EBELN.
• Add the columns from NEW_TABLE into the OLAP table.

Hope this helps!

Hey Greg,

thanks for the answer, we'll try this.

Hi Thomas - you're welcome. Please let me know how it goes.

Hi Thomas,

alternatively, you could add a custom field called EKGRP_K at the end of EKKO in the transformation that generates view EKKO so that when you upload it you have it available straight away.

For the SQL statement it would look like what Greg wrote:

SELECT DISTINCT

EKKO.*

,[...]

,E2.EKGRP AS EKGRP_K -- new line for purchasing group of related contract

FROM EKKO AS EKKO

[...]

LEFT JOIN EKKO AS E2 ON 1=1

AND EKKO.KONNR = E2.EBELN

AND E2.BSTYP = 'K' -- join only on documents of type Contract

And I suppose that you have extracted the contract header data from SAP too, because from what I remember, the standard connector filters on BSTYP = 'F' when extracting EKKO from SAP.

regards

Marc

Hi everybody,

thanks for your answers!

We tried both options and both work. Eventually we went with option 1 and it brings the results that we expect.

Hi @thomas.trill - glad to hear that this worked out. Best of luck with the use case!