Forum Discussion
Creating a Conditional Column Based on Specific Criteria
Hello,
I need help creating a new column based on the following conditions:
- If the **Settle_Name** is equal to "Card" or "OCCF", and
- The **Code** is equal to "000100160", and
- The **Number** length is 11, and
- The first 4 digits of the **Number** are "4398", and
- The last 4 digits of the **Number** are "1614", and
- The **Auth** field is not blank,
then return "valid"; otherwise, return "error".
**Column Value Formats**:
- **Settle_Name**: Text
- **Code**: Text
- **Auth**: Text
Thanks for the reply from rsbin.
Hi InsightSeeker ,
I have made some modifications to rsbin's formula and get the expected results for the data you have given.Since there are two "code" columns in the data you gave, use "code.1" instead for the second one:
Status = SWITCH ( TRUE (), ( [Code] = "000100160"|| [Code] = "000100234" ) && ( [Settle_Name] = "Card"|| [Settle_Name] = "OCCF" ) && LEN ( [Number] ) = 11 && LEFT ( [Number], 4 ) = "4398" && RIGHT ( [Number], 4 ) = "1614" && NOT ( ISBLANK ( [Code.1] ) ), "Valid", "Error" )Here is the same result as you show in the given table:
Best Regards,
Zhu
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
4 Replies
I would use the SWITCH function. Hope this works for you:
Validation = SWITCH( TRUE(), [Settle_Name] = "Card" || [Settle_Name] = "OCCF" && [Code] = "000100160" && LEN( [Number] ) = 11 && LEFT( [Number], 4 ) = "4398" && RIGHT( [Number], 4 ) = "1614" && ISBLANK([Auth]) = FALSE(), "Valid", "Error" )Regards,
Hi rsbin - My data looks like this. If I want to validate multiple codes with different criteria, how can I accomplish that?
Code 1
- If the **Settle_Name** is equal to "Card" or "OCCF", and
- The **Code** is equal to "000100160", and
- The **Number** length is 11, and
- The first 4 digits of the **Number** are "4398", and
- The last 4 digits of the **Number** are "1614", and
- The **Auth** field is not blank,then return "valid"; otherwise, return "error".
Code 2
- If the **Settle_Name** is equal to "Card" or "OCCF", and
- The **Code** is equal to "000100234", and
- The **Number** length is 11, and
- The first 4 digits of the **Number** are "4398", and
- The last 4 digits of the **Number** are "1614", and
- The **Auth** field is not blank,then return "valid"; otherwise, return "error".
For example:-
The data is in Table1.
settle_name Code Number Code Card 000100160 4398XX7614 162932 Card 000100160 4398XXX1614 101256 OCCF 000100160 4398XXX16145 105352 OCCF 000100234 14398XXX1614 105938 OCCF 000100160 4398XXX1614 106780 Card 000100160 4398XXX1614 Card 000100160 4398XXX1614 111260 Card 000100160 4398XXX1614 111496
Result
settle_name Code Number Code Status Card 000100160 4398XX7614 162932 Error Card 000100160 4398XXX1614 101256 Valid OCCF 000100160 4398XXX16145 105352 Error OCCF 000100234 14398XXX1614 105938 Error OCCF 000100160 4398XXX1614 106780 Valid Card 000100160 4398XXX1614 Error Card 000100160 4398XXX1614 111260 Valid Cash 000100160 4398XXX1614 111496 Error
InsightSeeker ,
So what you are saying is the Code is independent of your conditions. If I have this correct then simply remove the Code condition:Validation = SWITCH( TRUE(), [Settle_Name] = "Card" || [Settle_Name] = "OCCF" && LEN( [Number] ) = 11 && LEFT( [Number], 4 ) = "4398" && RIGHT( [Number], 4 ) = "1614" && ISBLANK([Auth]) = FALSE(), "Valid", "Error" )Does this work for you?
