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