Creating a Conditional Column Based on Specific Criteria

[InsightSeeker]

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

[rsbin]

@InsightSeeker ,

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,

[InsightSeeker]

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

[Anonymous · Accepted solution]

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 Team

If 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!

[rsbin]

@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?