Create Pseudo Table Join Between Three SAP Tables

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

Scenario:

Let us consider an example of table join between three SAP Tables, LTAP, LTAK and LTBK and the requirement is to extract the data from the following columns, and also other information:

Columns A and B contains data from LTAP (header) table, such as Warehouse Number/Warehouse Complex and Transfer Order Number. Columns C, D and E contains data from LTAK table. Columns F and G contains data from LTBK table. But, we extract data only related LTAP and a pseudo join is created between LTAP, LTAK, and LTBK tables.

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 Warehouse Number/Warehouse Complex and Transfer Order Number, which is extracted from LTAP, as a filter for extracting data from LTAK and Warehouse Number/Warehouse Complex and Transfer Requirement Number, which is extracted from LTAK, as a filter for extracting data from LTBK.

Concept:

  1. Create the three 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, and from the second Process file for the third Process file. This creates a pseudo table join.
  2. Connect the three Process files by Process Connection feature.
  3. Run the connected Process file.

To create pseudo table join from three 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 LTAP.
  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 table, 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 LTAK table, repeat the steps 2-6.
  8. Perform the following steps:
    1. To create a pseudo join, enter the filter to get Warehouse Number/Warehouse Complex and Transfer Order Number extracted from the first Process file.
    2. Save the Process file and select Run.
  9. To create a Process file using the LTBK table, repeat the steps 2-6.
  10. Perform the following steps:
    1. To create a pseudo join, enter the filter to get Warehouse Number/Warehouse Complex and Transfer Order Number extracted from the second Process file that was created based on LTAK, in columns C and E.
    2. Save the Process file and select Run.
  11. Connect the individual Process files as follows:
    1. Open the LTAP Process file, select Process Connection, and browse to find the LTAK Process file.
    2. Open the LTAK Process file, select Process Connection, and browse to find the LTBK Process file.

      3. The following screenshot illustrates the three connected Process files.

    3. Ensure that status and log columns from your first Process file is not overwritten by the data extraction of the second and third Process files. Also, update the End Row value to get the complete extraction from the second and third tables.

      For example, the following screenshot illustrates how to update the End Row for the first Process file.

      The following screenshot illustrates how to update the End Row for the second Process file.

      The following screenshot illustrates how to update the End Row for the third Process file.

  12. Save the connected Process file and select Run. The Excel file is displayed with the data from the three tables, LTAP, LTAK and LTBK.

For information about how to extract data from three tables using the dynamic table join condition functionality, see Extract Data From Three SAP Tables Using Dynamic Table Join Feature.

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