How to create a Case Log in alteryx from SAP tables?

Question

Hi,

I am trying to create a case log from SAP tables for purchases. I have EKPO, EKKO, RSEG..etc tables.

I'd like to create a new file based on these separate tables to have the below (and maybe add more attributes if possible):

CASE_KEY – Identifies the process instance (purchase order number for example).

ACTIVITY – Describes the business action ("Create PO", "Post Invoice" or "make payment").

TIMESTAMP – Exact date and time of the event (including hours, minutes, and seconds).

Could you please guide on me on how to connect those tables together to form a new case log table?

Thank you

PakinamBakr · Answer

Thank you very much that's very helpful.

Now the areas I'm struggling with are adding attributes and transforming data.

I created the case key column using the PO number. However, I am not able to:

1. Add this in a new table (it gets added as an additional column on the join output).

2. I am not sure which alteryx tool to use to define the activity and the logic. Could you share more guidance on this please

3. Is there a way to rename all columns at once? When combining the tables, the columns are very confusing, so adding EKKO for example at the beginning of every column name would be useful.

4. Can I please ask what do you mean by "Use the Formula tool to create calculated fields for CASE_KEY, ACTIVITY, and TIMESTAMP.".  Why do we need to create this step

5. If I'd like to add more attributes, could you please advise what's the best alteryx tool to use for this?

Really appreciate your help with this.

Thanks very much

Warcry · Answer

* Connect to SAP Tables:

* Use the SAP Input Tool in Alteryx to connect to your SAP system and extract data from the EKPO, EKKO, and RSEG tables. Ensure you have the necessary credentials and permissions to access these tables.

* Join Tables:

* Use the Join tool in Alteryx to combine the tables based on common keys. For example:

* Join EKPO (Purchase Order Items) with EKKO (Purchase Order Header) using the Purchase Order Number (EBELN).

* Join the resulting dataset with RSEG (Invoice Receipt) using the Purchase Order Number and other relevant keys like Item Number (EBELP).

* Add Attributes:

* Create the CASE_KEY by using the Purchase Order Number (EBELN) as the unique identifier.

* Define the ACTIVITY column by mapping specific actions to the data. For example:

* "Create PO" could be derived from EKKO creation timestamps.

* "Post Invoice" could be derived from RSEG invoice posting data.

* "Make Payment" could be added if you have payment data from another table.

* Extract the TIMESTAMP from the relevant date and time fields in the tables (e.g., AEDAT for creation date, BUDAT for posting date).

* Transform Data:

* Use the Formula tool to create calculated fields for CASE_KEY, ACTIVITY, and TIMESTAMP.

* Use the Select tool to rename and organize columns as needed.

* Output the Case Log:

* Use the Output Data tool to save the resulting case log table to your desired format (e.g., CSV, Excel, or database).

* Optional Enhancements:

* Add more attributes by including additional tables or fields, such as vendor details or payment terms.

* Use the Filter tool to include or exclude specific records based on your criteria.