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
Solved! Go to Solution.
_
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 😀👍
Mine still does not work. 😭
I checked that [Date] variable is Date type.
My formula is as follows
error
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.
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~"
Hi @AliciaL ,
I was able to replicate the issue. Even i getting Null. Working on the fix will get back to you.
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😀👍
Thank you soooo much!
It's working now.
Have a great day 😁
Happy to help 🙂
Cheers and happy analyzing 😀