cancel
Showing results for 
Search instead for 
Did you mean: 

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

ivan_camac
Participant
0 Kudos
211

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 MonthEnd MonthDateCost CentreDatasourceGL AccountEmployeeHR AccountFlagFTE
2024072029060000008216BW#7048##1
2024072029060000008216INPUT#7048FORTNIGHTLY_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 

View Entire Topic
ivan_camac
Participant

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
Product and Topic Expert
Product and Topic Expert
0 Kudos

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
Participant
0 Kudos

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
Product and Topic Expert
Product and Topic Expert
0 Kudos

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