4 weeks ago
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:
Any guidance on structuring this condition or alternatives for achieving the desired result would be greatly appreciated!
Thank you!
Request clarification before answering.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
User | Count |
---|---|
77 | |
29 | |
9 | |
7 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.