Review by ID number and flag based on meeting conditions

[jw68937]

Hello, I'm trying to build a data check based on the conditions below, any suggestions on how to go about this? 

For each Control UID number:

• If Test 0, Test 1, Test 2, OR Test 3 have a Status other than ‘Not in Scope’, the Significance field should be Key
• If Test 0, Test 1, Test 2, AND Test 3 are all ‘Not in Scope’, the Significance field should be Non-Key

If they don't meet the conditions I want to create a summary by control ID along with a comment on the issue

Results would look like this, one row per control ID and a comment of the issue

Control UID	Check
BU-DB.FA.S03.FAC5.DB	Should be Not Key
BU-DB.INVC.S08.INVC2.1A.DB	Should be Not Key
BU-DB.INVC.S08.INVC2.1B.DB	Should be Not Key
xyz...	Should be Key

Thanks for any ideas!

[NMangera]

@jw68937,

one solution is to create a flag that assigns a 1 or 0 based on Status. From here the summarise tool can be used to group the records by ID. Followed by an IF statement that counts the number of records 'Not in Scope'. Just be sure the logic captures all the different edge cases with the full dataset

[nagakavyasri · Accepted solution]

Sample workflow of your condition:

[chukleswk]

You can use a flow like the following to perform your check:

[chukleswk]

I have modified it to include some of the above responses and run with less tools but still give you the same output: