Forum Discussion

InsightSeeker's avatar
InsightSeeker
Helper III
1 year ago
Solved

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 **Nu...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
rsbin
Community Champion
1 year ago

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's avatar
InsightSeeker
Helper III
1 year ago

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_nameCodeNumberCode
Card0001001604398XX7614162932
Card0001001604398XXX1614101256
OCCF0001001604398XXX16145105352
OCCF00010023414398XXX1614105938
OCCF0001001604398XXX1614106780
Card0001001604398XXX1614 
Card0001001604398XXX1614111260
Card0001001604398XXX1614

111496

 

Result

 

settle_nameCodeNumberCodeStatus
Card0001001604398XX7614162932Error
Card0001001604398XXX1614101256Valid
OCCF0001001604398XXX16145105352Error
OCCF00010023414398XXX1614105938Error
OCCF0001001604398XXX1614106780Valid
Card0001001604398XXX1614 Error
Card0001001604398XXX1614111260Valid
Cash0001001604398XXX1614111496

Error

 

  • rsbin's avatar
    rsbin
    Community Champion
    1 year ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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!