IF formula with Date condition

[AliciaL]

Hi I'm new to alteryx and have been trying to use a formula tool to categorize data by following conditions

Payment	Date	fee(%)
Credit Card	~2019-07-30	0.017
	2019-07-31~2020-06-30	0.022
	2020-06-30~2020-07-31	0.0209
	2020-08-01~	0.0192
Debit Card	~2020-07-31	220
	2020-08-01~	198

I'm getting the "invalid type in operator <" error from this formula.
I'm not sure what is wrong about my formula.
Can someone help me out?

IF [Payment]="CreditCard" and [Date] < "2019-07-31"
THEN "CreditCard, 2019-07-30"
ELSEIF [Payment]="CreditCard" and "2019-07-30" <[Date]<"2020-07-01"
THEN "CreditCard, 2019-07-31~ 2020-06-30"
ELSEIF [Payment]="CreditCard" and "2020-06-30"<[Date]<"2020-08-01"
THEN "CreditCard, 2020-07-31~ 2020-07-31"
ELSEIF [Payment]="CreditCard" and "2020-07-31"<[Date]
THEN "CreditCard, 2020-08-01~"
ELSEIF [Payment]="DebitCard" and [Date]<"2020-08-01"
THEN "DebitCard, ~2020-07-31"
ELSE "DebetCard, 2020-08-01~"
ENDIF

[atcodedog05]

_

[atcodedog05]

Hi @AliciaL ,

I tried your formula it seem to work. 

Input:

Output :

Workflow :

Formula

Hope this helps 🙂

Please provide your workflow and Data to help better. If this helps give a Like if you dont mind 😀👍

[AliciaL]

Mine still does not work. 😭

I checked that [Date] variable is Date type. 

My formula is as follows

error

[atcodedog05]

Hi @AliciaL 

This is just a warning not a error. It should still work.

Did my workflow work for you.

Can you provide your that part of the workflow and some sample data to troubleshoot it.

[AliciaL]

Thank you for the help!

Workflow

I used select and sample tool after filter tool to only few samples of raw data. (so it's not an important part) 

Formula tool Input data 

Formula tool configuration

Formula tool output data

output is shown as [Null] but since the input data is 2020-08-01, output shoud be "Creditcard, 2020-08-01~"

[atcodedog05]

Hi @AliciaL ,

I was able to replicate the issue. Even i getting Null. Working on the fix will get back to you.

[atcodedog05 · Accepted solution]

Hi @AliciaL ,

These lines where creating an issue.

Its not a proper complete condition we cant apply two conditions like that. Hence it was affecting the later statements.

Changing to below works.

IF [Payment]="CreditCard" and [Date] < "2019-07-31"
THEN "CreditCard, 2019-07-30"
ELSEIF [Payment]="CreditCard" and "2019-07-30" <[Date] and [Date]<"2020-07-01"
THEN "CreditCard, 2019-07-31~ 2020-06-30"
ELSEIF [Payment]="CreditCard" and "2020-06-30"<[Date] and [Date] <"2020-08-01"
THEN "CreditCard, 2020-07-31~ 2020-07-31"
ELSEIF [Payment]="CreditCard" and [Date] > "2019-07-31"
THEN "CreditCard, 2020-08-01~"
ELSEIF [Payment]="DebitCard" and [Date]<"2020-08-01"
THEN "DebitCard, ~2020-07-31"
ELSE "DebitCard, 2020-08-01~"
ENDIF

Output :

Workflow is attached check it out.

Please check and let me know.

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind😀👍

[AliciaL]

Thank you soooo much!

It's working now.

Have a great day 😁

[atcodedog05]

Happy to help 🙂

Cheers and happy analyzing 😀