Create Pseudo Table Join Between Two SAP Tables

This topic describes how to create a dynamic table join from two SAP tables in Process Runner using the Process Connection and data filter builder functionality.

Scenario:

Let us consider an example of table join between two SAP Tables, MARA and MAKT and the requirement is to extract the data from the following columns, and also other information:

Columns A to G contains data from MARA (header) table, such as Material Number, Created On, Material Type, Material Group, Gross Weight, Net Weight, and Base Unit of Measure.

Column H contains data from MAKT (Material Description) table. But, we extract data only related to MARA and a pseudo join is created between MARA and MAKT.

This is not a SQL-type join; you must first extract data from the first or parent SAP table, and then extract data from the second or child table filtering from the parent SAP table data and use the data from parent table as the filtering condition. In this case, we are using Material Number that is extracted from MARA table as a filter for extracting data from MAKT table.

Concept:

  1. Create the two Process files individually and add the filter to provide the source value as Excel columns from the first Process file for the second Process file. This creates a pseudo table join.
  2. Connect the two Process files by Process Connection feature.
  3. Run the connected Process file.

To create pseudo table join from two SAP tables

  1. Open Process Runner and select New Process. The New Process window is displayed.
  2. Select Data Extractor module and in the Table/View Name box, enter MARA.
  3. Select More and clear Automap all fields. You can select the required fields manually.
  4. Select OK. The Magnitude Connector for SAP window is displayed. Perform the following steps:
    1. Select the SAP system, select Logon, and enter the information for Client, User, Password, and Language.
    2. Select OK. Process Runner displays a message to indicate that the tables are validated and that you must update the required mappings.
  5. Go to the Mapper tab, select the required fields from the tables, and map the fields to the Excel columns by entering the map values or using the drag-and-drop functionality.
  6. Save the Process file and select Run.
  7. To create a Process file using the MATK table, repeat the steps 2-6.
  8. On the Mapper tab, select the required fields from this table and map those fields to the Excel columns.
  9. Perform the following steps:
    1. To create a pseudo join, enter the filter to get the description of material extracted from the first Process file; optionally, enter the second filter to ensure that you extract only the description in English language.
    2. Save the Process file and select Run.
  10. Select Save > Run.
  11. Connect the individual Process files as follows:
    1. Open the MARA Process file, select Process Connection, and browse to find the MAKT Process file.
    2. Ensure that the status and log columns from your first Process file is not overwritten by the data extraction of the second Process file. Also, update the End Row value to get the complete extraction from the second table.
  12. Save the connected Process file and select Run. The Excel file is displayed with the data from the two tables, MARA and MAKT.

If you are extracting a table join between Header Level table and Detail / Line Item level table, there is a possibility of one-to-many relationship that could cause mismatch in lines.

In this case, we suggest you extract the Detail / Line item level table first and then extract the Header level using filters.

For information about how to extract data from three tables using Process Connection functionality, see Create Pseudo Table Join Between Three SAP Tables.