Validation using multiple Conditions for Multi-row datasets

[Rumanais]

Hello,

I am trying to build a workflow with the desired outcome stated within the excel file (attached) for few sample dummy accounts.

Goal: Validate the accounts with stated logic which will give me the desired output stated within the Input excel file. The desired logic I am trying to build is  within the attached workflow. However, since the logic should apply on random  number of records for each account (some account have 10 instances, some have 5 or some has 3 etc.), the logic is not working for the simple formula tool I used. 

Need help with building the desired logic within the workflow please. 

Thank you!

[binuacs]

@Rumanais 

[Rumanais]

hello binuacs,

Let me clarify the ask a little further using the account # X000ABAB as an example:

for this account, the MISC_AM of 772.82 is equal to the RunTot_TXN_AM of 772.82 on the TXN_EFF_DT of 12/16/2021 which is the same date as TRMNT_DT. So, the Row # 4 where this condition (if the sum if TXN_AM for the TXN_EFF_DT prior or equal to the TRMNT_DT is equal to the MISC_AM then 'PASS') is met, the row # 4 would say "PASS" under the Output column (validation result). But for the rest of the rows for that account will say "Scope_act".

Output column (validation result):

1. For account Number X000ABAB with 10 instance/row of this account, row # 4 would say "PASS", the rest of the rows will read "Scope_act". Same logic 

for Account #s Y000CDCD & Z878FGHK but with different number of instance/rows for each account.

2. For account Number W2527IJKD (the last account in the list), since the logic does not meet for any of the instance/rows for this account, the first instance where the logic was closely met would say "REVIEW/FAIL", the rest of the rows will read "Scope_act".

Hope this helps. we can also chat if any further clarification is needed for this ask.

Thank you for taking a stab at this!

[binuacs · Accepted solution]

@Rumanais please check the comment column

[Rumanais]

Hi binuacs,

This worked perfectly for what I was trying to accomplish! Thank you so much.

Since I am relatively new to Alteryx, just curious, instead of writing the logic in the formula tool, do you think we could have achieve this result using other Alteryx tools?

Thanks again.