Handling Null and Empty Values in Join Function using Auto filling Option

[paulgabog]

SAP Profitability and Performance Management (PaPM) Join Function is referred to as a processing function that is used to combine two or more inputs, either data source function such as model table or other processing functions such as allocation and calculation, to form an enriched result based on the settings configured on the function.

During data preparation or checking of results, there are two common data values which I encounter namely, Null and Initial. In SAP PaPM, both Null and Initial are represented by an empty cell for characteristic and “0” for the key figure, respectively.

As defined in PaPM Application Help Join function section, the modeler or user must take care of these values when processing input data in Join because the response of the two values are different:

• Null (?) does not have any value and does not occupy memory. This can be achieved if a field was not assigned with values initially.


• Initial (' ') does contain a value and so occupies memory. In SAP PaPM, a cell can have an initial value if it was assigned with an empty record.

How can we know that we have null values present in the input or result? How can these empty and null values affect my configuration result and how can we handle it? These are several questions which pop to my mind when I do join function configuration in PaPM.

Today in this blog, I will explain how to use a feature in Join called Auto filling, to fill the gap on how to handle empty and null values moving forward for further processing.

Input Data

Initially, I have prepared three (3) SAP HANA Tables for the inputs to be used, see below tables. Table PH and Table US consist of three fields wherein one Characteristic Field (Customer) has data values for empty string represented by ‘’ and null value represented by ?. Table DE on the other hand, consists of data without empty string nor null value.

Below you can see the data enveloped inside these SAP HANA Tables as well as how it looks like from PaPM perspective.

Disclaimer: For visualization purposes and continuity of blog, I placed question mark for null (?) and quote ('') for empty or initial value. This may vary though, in the case of analyze screen in PaPM, both null and empty values are represented by number sign (#). And in the case that you are using it already in a PaPM function, it will automatically be converted to empty cell for Characteristic and zero (0) for Key Figures.

Below are the original form of data and this will be used for the explanation part of the scenario.

Table US

Table DE

Table PH

In system and database perspective, below is how these data records look like:

A) SAP HANA Studio

B) PaPM Analyze Screen

C) PaPM Results List after run initiation

 

Join Configuration

Now that you get an overview of our input data, let us proceed with the scenarios. Left Outer Join function will be utilized in the scenarios. This is a type of Join function which returns all records and fields from the first table then adds distinct fields from the other tables where the predicates match.

Join Function used in the scenarios are composed of three rules, one (1) From Rule with two (2) Left Outer Join Rules. To make the scenario simpler and easier to understand, a lone join predicate Product field will be used.

Please refer to the embedded link to know more about Join functionalities.

The specific Auto Filling options which will be used to steer the behavior of left outer join has the following types:

1. No: This option will not return rows with null values.


2. If Null, then First to Last: The first non-null value is taken and if all values are null then the initial value is returned for that field.


3. If Null/Initial, then First to Last: The first non-null and non-initial value is taken and if all values are null or initial, then an initial value is returned for that field.

These options can be defined in the Header section and Left Outer Join Type on the other hand is defined in Rules Tab section.

 

Scenario

The scenario is set up using the configuration in the above image. It was intentional to use same inputs as presented below.

In addition join types and configuration is also the same with slight difference on auto filing options to show that results can differ depending on what Auto filling option is used. The resulting table will have the following fields – Product, Customer, Quantity, Amount, Price.

Now, what data should Customer field contain? Should the data be collected from PH, US, or DE Table? This will be explained further in the last section of this blog where I will be explaining the Auto filling options.

1) Auto Filling: No

When Autofilling option is set to No, the condition will not return rows with null values. PROD04 row contains a null value for Customer Field from PH table, therefore, this row will not be returned. PROD05 on the other hand will take the non null value from the Customer Field of PH table and this row will be returned.

Input Table

After activating the function and run is initiated, the resulting value will look like below in PaPM side:

In addition, an error message from PaPM will be added too as described in the image below.

The NULL values were caught by an error handler that informs user that there are null values in the result of the function run. In order for these null values to be included in the results, the Auto filling option If Null then first to last is introduced which will be demonstrated on Scenario 2.

2) Auto Filling: If Null then first to last

The scenario will follow the condition that if there is a null value in the data set, the first non-null value is taken and if all values are null then an initialized value is returned for that field. PROD04 row contains a NULL value for Customer field in PH table therefore we will use the Initial value from US table,. In PROD05 row, the initial value of Customer Field from PH table will be used.

Input Table

After activating the function and run is initiated, the resulting value will look like below in PaPM side:

In the case that there is a requirement to return all values without any null data and possibly minimal initialized value in the resulting table, the third Auto filling option If Null/Initial then first to last is introduced.

3) Auto Filling: If Null/Initial then first to last

This scenario will look into all null and initial data in the data set and will follow the condition that the first non-null and non-initial value is taken and if all values are null or initial, then an initial value is returned for that field. PROD04 row contains a NULL value for Customer field in PH table and Initial for US table, therefore the value of Customer Field from DE Table., DE_CUST04, will be consumed as it is a non-null and non-initial value.

In PROD05 row the scenario is the same, since there is an Initial value for Customer field in PH table and NULL for US table, we will get DE_CUST05 from DE Table as the value of Customer field.

Input Table

After activating the function and run is initiated, the resulting value will look like below in PaPM side

To conclude,

• For users to check if there are null values present in the input, user can check it via SAP HANA Studio and open content definition of the table.

 

• To check if there are null values in the results of Join, users should set auto filling option to No which will then result to an error message after the function has been run .

 

• The introduction of the other two options for auto filling is useful in handling the values which you want to have in result of Join. As clearly seen in the discussed scenarios, users can see the significant difference in the results when choosing one of these options.

Pretty much it for the use of Auto filling options and Hope you found it useful. I hope you learned a thing or two from this blog and so best of luck on your configuration battles!

For more questions, please feel free to leave a comment below. I also encourage you to post some questions through https://answers.sap.com/questions/ask.html and use primary tag: SAP Profitability and Performance Management or user tag: PaPM before submitting.

Who knows? Maybe your question might be chosen as a topic of the upcoming blogs. Ciao!