SAC Advanced Formula - Conditional Check for Existence of Records Across a Scope in FOREACH Loop

[ivan_camac]

Hi SAP Community,

I'm working with an advanced formula in SAP Analytics Cloud (SAC) Planning to selectively copy data records based on a condition. The goal is to check if an employee has any record with Datasource = "INPUT" and, if so, to copy that employee's records from Datasource = "BW" to new records under Datasource = "BW_Override". For employees without any INPUT records, no action should be taken.

Here is a simplified structure of my data:

Start Month	End Month	Date	Cost Centre	Datasource	GL Account	Employee	HR Account	Flag	FTE
202407	202906	000000	8216	BW	#	7048	#	#	1
202407	202906	000000	8216	INPUT	#	7048	FORTNIGHTLY_SALARY	#	0.5
...	...	...	...	...	...	...	...	...	...

To achieve this, I'm trying to use a FOREACH loop with an IF condition. My current approach is as follows:

CONFIG.TIME_HIERARCHY = CALENDARYEAR
CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE = OFF
CONFIG.GENERATE_UNBOOKED_DATA = OFF

// Define the scope for relevant Datasource values
MEMBERSET [d/PLNHR_DATASOURCE] = ("INPUT", "BW", "BW_Override")

FOREACH.BOOKED [d/PLN_EMPLOYEE]
    // Broad-scope check if there's any record with Datasource = INPUT for the current employee
    IF RESULTLOOKUP([d/PLNHR_DATASOURCE] = "INPUT") > 0 THEN
        // Copy records from BW to BW_Override for employees with INPUT records
        DATA([d/PLNHR_DATASOURCE] = "BW_Override", [d/PLN_FLAG] = "X") = RESULTLOOKUP([d/PLNHR_DATASOURCE] = "BW")
    ENDIF
ENDFOR

However, it seems the IF condition is not functioning as expected. I suspect RESULTLOOKUP in the IF statement may be performing a set-wise check rather than aggregating across all records for each employee in the loop, so it fails to correctly detect the presence of Datasource = "INPUT".

My questions:

1. Is there a way in SAC advanced formulas to check for the existence of records within a scope (e.g., if any record exists with Datasource = "INPUT" for an employee)?
2. How can I ensure the IF condition only triggers when at least one Datasource = "INPUT" record is found for the employee, rather than checking on a set-wise basis?

Any guidance on structuring this condition or alternatives for achieving the desired result would be greatly appreciated!

Thank you!

SAP Analytics Cloud for planning SAP Analytics Cloud 

[ivan_camac · Accepted solution]

Just to answer my own question, this is the solution I came up with which is now working:

// Turn off unbooked data generation to ensure only booked (non-empty) data is considered
CONFIG.GENERATE_UNBOOKED_DATA = OFF

// Define the scope for Datasource dimension to include only the relevant values: INPUT, BW, and BW_Override
// This limits calculations to these specific Datasource values for improved efficiency
MEMBERSET [d/PLNHR_DATASOURCE] = ("INPUT", "BW", "BW_Override")

// Loop through each combination of booked records for Employee and Datasource dimensions
FOREACH.BOOKED [d/PLN_EMPLOYEE], [d/PLNHR_DATASOURCE]

    // Check if the current Datasource for this employee is "INPUT"
    IF [d/PLNHR_DATASOURCE] = "INPUT" THEN
        
        // If an INPUT record exists for this employee, copy the records from "BW" to "BW_Override"
        // Set the flag (PLN_FLAG) to "X" on the copied records to indicate they have been overridden
        DATA([d/PLNHR_DATASOURCE] = "BW_Override", [d/PLN_FLAG] = "X") = RESULTLOOKUP([d/PLNHR_DATASOURCE] = "BW")
        
        // Delete the original "BW" records for this employee once they have been copied to "BW_Override"
        DELETE([d/PLNHR_DATASOURCE] = "BW")
        
        // Delete any "BW_FTE" records for this employee as well, as these are no longer needed after override
        DELETE([d/PLNHR_DATASOURCE] = "BW_FTE")
        
    ENDIF

// End of the loop that processes each booked Employee-Datasource combination
ENDFOR

[MoonJun · Accepted solution]

Hi @ivan_camac

Above your script, the FOREACH & IF condition(Lines 9 & 12) is unnecessary to get your expected result.  FOREACH iteration is taking more execution time and your Advanced Formulas script can achieve better performance without using the FOREACH statement. Please validate the calculated result without the FOREACH statement.  

• MEMBERSET [d/PLNHR_DATASOURCE] = ("INPUT", "BW", "BW_Override")

  DATA([d/PLNHR_DATASOURCE] = "BW_Override", [d/PLN_FLAG] = "X") = RESULTLOOKUP([d/PLNHR_DATASOURCE] = "BW")
  DELETE([d/PLNHR_DATASOURCE] = "BW")
  DELETE([d/PLNHR_DATASOURCE] = "BW_FTE")

Moonjun

[ivan_camac · Accepted solution]

Thanks Moonjun,

But my requirement is that the records are only moved if there is an 'INPUT' record existing for that employee. My understanding of the version you have supplied is that it would move ALL records from BW to BW_Override for all employees, even if they don't have an 'INPUT' record.

Cheers, Ivan.

[MoonJun · Accepted solution]

Hi @ivan_camac

I understand your point and the advanced formulas script.

Technically, the script  IF [d/PLNHR_DATASOURCE] = "INPUT" THEN  does not check the 'INPUT' record for that employee, but can check the existing record in the FOREACH.BOOKED statement. It is a genius solution to meet your expectations. The concern is that the execution time could be increased based on the number of Employees (=number of Fact records).

Moonjun