Validation Formula giving wrong comments

[Ben020283]

I have an Alteryx formula to validate certain columns and produce desired comments. I am getting incorrect comments for one of the validation. below is the formula.

IF ISNULL([Termination Date]) THEN "Termination Date is Blank"

ELSEIF [Termination Date] > [Ed Date] THEN

"No action needed, Termination date is future dated"

ELSEIF [Termination Date] < [St Date] AND [Result Line Amount] != 0 AND NOT ISNULL([Retro Period]) THEN

"No action required, Retro Pay"

ELSEIF [Termination Date] < [ST DATE] AND [RESULT LINE AMOUNT] = 0 THEN

"No action needed, Employee is not getting any pay in current oncycle paycheck"

ELSEIF [TERMINATION DATE] > [ST DATE] AND [TERMINATION DATE] <= [ED DATE] AND [RESULT LINE AMOUNT] = 0 THEN

"Need to check for Missing pay"

ELSEIF [TERMINATION DATE] > [ST DATE] AND [TERMINATION DATE] <= [ED DATE] AND [RESULT LINE AMOUNT] > 0 THEN

"Need to check, Employee is terminated during the pay period " +[Termination Date]

ELSEIF [TERMINATION DATE] < [ST DATE] AND [RESULT LINE AMOUNT] > 0 AND

NOT CONTAINS([Earning], "Salary Base Pay - BASE") OR NOT CONTAINS([EARNING], "Hourly - Regular - REGH") THEN

"Prior date termination receiving earning other than reg. Validate for Bonus or OTP"

ELSEIF [TERMINATION DATE] < [ST DATE] AND [RESULT LINE AMOUNT] > 0 AND

(CONTAINS([EARNING], "Salary Base Pay - BASE") OR CONTAINS([EARNING], "Hourly - Regular - REGH")) THEN

"Prior date termination receiving Reg Sal. Possible OP"

ELSE

"Need to check"

ENDIF

for one of the scenario i am getting comment "Prior date termination receiving earning other than reg. Validate for Bonus or OTP" can someone review the formula and highlight what's wrong.

Employee Status	Termination Date	Earning	Retro Period	Result Line Hours	Result Line Amount	St Date	Ed Date
Terminated	7/1/2023	Salary Base Pay - BASE		0	0	7/1/2023	7/15/2023

[alexnajm]

Are your dates being stored as actual dates values (i.e. 2023-07-01) or are they still strings? I would convert them to date values using three DateTime tools or a Multi Field Formula to see if that helps.

[Ben020283]

Thanks for prompt response.

They are in proper date format.

I copied the below from browse tool

 Semi-Monthly On-cycle 07/01/2023 - 07/15/2023 (Semi-monthly) 2023-07-14 Terminated 2023-07-01 Personal Reasons [Null] [Null] Salary Base Pay - BASE [Null] 0 0 [Null] [Null] [Null] 2023-07-01 2023-07-15 Prior date termination receiving earning other than reg. Validate for Bonus or OTP

[alexnajm]

If this is copied from the Browse tool, I am doubtful it's in the proper ISO date format - Alteryx only uses YYYY-MM-DD! Go ahead and double check your metadata.

[Ben020283]

Below is the Metadata

Name	Type	Size
Worker	V_String	255
Colleague ID	V_String	255
Company	V_String	255
Pay Group	V_String	12
Payroll Result Pay Cycle Type	V_String	255
Period	V_String	255
Payment Date/Reversal Date	Date	10
Employee Status	V_String	255
Termination Date	Date	10
Termination Reason	V_String	255
Leave Type	V_String	1
First Day of Leave	Date	10
Earning	V_String	255
Retro Period	V_String	255
Result Line Hours	Double	8
Result Line Amount	Double	8
Result Line Amount Source	V_String	255
Batch ID	V_String	255
Payroll Input Comments	V_String	255
St Date	Date	10
Ed Date	Date	10
Comments	V_WString	1073741823

[jrgo · Accepted solution]

@Ben020283 

I believe the issue is that you're not grouping the OR conditions for that IF clause (2nd to last). You are on the last one so I'm thinking it was simply some oversight when writing the expression. 

IF ISNULL([Termination Date]) THEN "Termination Date is Blank"

ELSEIF [Termination Date] > [Ed Date] THEN

"No action needed, Termination date is future dated"

ELSEIF [Termination Date] < [St Date] AND [Result Line Amount] != 0 AND NOT ISNULL([Retro Period]) THEN

"No action required, Retro Pay"

ELSEIF [Termination Date] < [ST DATE] AND [RESULT LINE AMOUNT] = 0 THEN

"No action needed, Employee is not getting any pay in current oncycle paycheck"

ELSEIF [TERMINATION DATE] > [ST DATE] AND [TERMINATION DATE] <= [ED DATE] AND [RESULT LINE AMOUNT] = 0 THEN

"Need to check for Missing pay"

ELSEIF [TERMINATION DATE] > [ST DATE] AND [TERMINATION DATE] <= [ED DATE] AND [RESULT LINE AMOUNT] > 0 THEN

"Need to check, Employee is terminated during the pay period " +[Termination Date]

ELSEIF [TERMINATION DATE] < [ST DATE] AND [RESULT LINE AMOUNT] > 0 AND

NOT CONTAINS([Earning], "Salary Base Pay - BASE") OR NOT CONTAINS([EARNING], "Hourly - Regular - REGH") THEN

"Prior date termination receiving earning other than reg. Validate for Bonus or OTP"

ELSEIF [TERMINATION DATE] < [ST DATE] AND [RESULT LINE AMOUNT] > 0 AND

(CONTAINS([EARNING], "Salary Base Pay - BASE") OR CONTAINS([EARNING], "Hourly - Regular - REGH")) THEN

"Prior date termination receiving Reg Sal. Possible OP"

ELSE

"Need to check"

ENDIF

[Ben020283 · Accepted solution]

It worked. Have to change the validation sequence as well. Thanks for help.