Pseudo Table Join/Multiple table extraction of three SAP tables using Process Runner |
We can create a pseudo table join from more than one SAP table using Process Runner. This can be done by Process Connection functionality and Data Filter Builder. For the context of this let us walk through a simple example of table join between 3 SAP tables.
Scenario:
Create a join between SAP tables LTAP, LTAK and LTBK into a single excel worksheet named LTAK-LTAP-LTBK.xlsx using Process Runner. The requirement is to extract Warehouse Number/ Warehouse Complex, Transfer Order Number and Transfer Requirement Number and other information.
Concept:
1)Create three process files individually. a.Create first process file by New Process Wizard. b.Map the excel column to respective SAP fields c.Test the process file by executing it once. d.Save the process file e.Repeat steps ‘a’ and ‘b’ for second and third process file f.Add filter and provide source value as Excel columns from first process file. This will help create a pseudo table join. g.Test the process file by executing it once. h.Save the process file
2)Connect the three process files by Process Connection feature. 3)Execute the whole process. Approach:
Column A and B will contain data from LTAP (header) table like Warehouse Number/Warehouse Complex and Transfer Order Number. Column C, D and E will contain data from LTAK table. Column F and G will contain data from LTBK table. But we will be extracting data only related to data extracted from LTAP. This creates a pseudo join between LTAP, LTAK and LTBK.
This is not a SQL-type join so you have to first extract data from first / parent SAP table, then extract data from second / child table filtering from the parent SAP table data and use the data from parent table as a filtering condition. In this case, we will be using Warehouse Number/Warehouse Complex and Transfer Order Number, which is the 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.
Please note that you have to create these process files individually and then make a connection. 1.Open Process Runner and Launch ‘New Process’ Wizard. 2.Select Data Extractor module and enter LTAP in the Table/View name text box and click OK. Also, make sure you uncheck the ‘Automap all fields’ box for selecting the desired fields manually.
3.Select the SAP system and enter logon credentials. 4.Go to Home Tab and select the external excel file. 5.Go to the ‘Mapper’ tab and select all the fields required from this table. Map the fields to the Excel columns by drag and drop.
6.Save this file. 7.Test the process file by clicking Run. 8.Launch ‘New Process Wizard’ again. (Repeat steps 2-7 as described below) 9.Select Data Extractor module and enter LTAK in the Table/View name text box and click OK. Again, make sure you uncheck the ‘Automap all fields’ box for selecting the desired fields manually.
10.Select the SAP system and enter your credentials. 11.Go to Home Tab and select the external excel file.
12.Go to the ‘Mapper’ tab and select all the fields required from this table. Map the fields to the Excel columns by drag and drop.
13.Now, we have to enter the filter to get Warehouse Number/Warehouse Complex and Transfer Order Number extracted from first process file.
This step is important to create the pseudo join. As you can see from the above screenshot, you are filtering Warehouse Number/Warehouse Complex extracted by LTAP process file in column A and Transfer Order Number extracted by LTAP process file in column B.
14.Save the file. 15.Test the process file by clicking Run. 16.Select Data Extractor module and enter LTBK in the Table/View name text box and click OK. Again, make sure you uncheck the ‘Automap all fields’ box for selecting the desired fields manually.
17.Select the SAP system and enter your credentials. 18.Go to Home Tab and select the External file.
19.Go to the ‘Mapper’ tab and select all the fields required from this table. Map the fields to the Excel columns by drag and drop.
20.Now, again we have to enter the filter to get Warehouse Number/Warehouse Complex and Transfer Requirement Number extracted from second process file (LTAK).
This step is important to create the pseudo join. As you can see from the above screenshot, you are filtering Warehouse Number/Warehouse Complex and Transfer Requirement Number extracted by LTAK process file in column C and E. 21.Save the file. 22.Test the process file by clicking Run.
23.We have created three process files individually, so now our task would be to connect or chain these process files.Open LTAP process file and go to Advance tab. Select “Enable Connection” and click on folder browse button to find LTAK process file.
24.It should look like this.
25.Similarly, now open LTAK process file and go to Advance tab. Select “Enable Connection” and click on folder browse button to find LTBK process file.
26.It should look like as below.
27.Finally all three process files connected will look like as shown in below screenshot.
28.Last step would be ensuring that status and log columns from your first process file is not overwritten by the data extraction of second and third process file. Also, adjust the End Row value to make sure you get full extraction from extraction of second and third table. You need to fix this value for all three process files. In this example, it should look something like this. For first process LTAP:
For second process LTAK: For third process LTBK: 29.The final result should be like this
Next: |