Skip to main content
Topics#Data ManagementRonald Olech 的问题
Ronald Olech (Comprehense) 提问于 #Data Management
2015年11月19日 15:11

Can I construct a CASE function with multiple values for each result?

Hi all,  I have a need to set a field with 8-9 values (county), based on a text field (city) that will contain about 100 values.  (FYI - if the geographic regrion were bigger, I'd be pushing for using an address verification, but I digress).  I was hoping I could use something like this

 

CASE(billingCity,

 

("City1" || "City2"), county1,

 

("City3" || "City4"), county2,

 

"no match")

 

This throws an erroe as does  the Or() function because they only operate on boolean values.  Anysuggestions on how to make this work?  I do not want to have to write 100 case statements if I can do it more effeciently.
3 个回答
  1. Deepak K Anand (‎‎‎‎‎‎Zoom Communications, Inc.)
    2015年11月19日 15:17

    If you were to use a CASE then you'll need to do it like this - 

    CASE(
        BillingCity,
        "City1","County 1",
        "City2","County 1",
        "City3","County 2",
        "City4","County 2",
        "No Match"
    )

     

    Which means you cannot write write expressions on the CASE branches. These needs to be a finite value.

     

    If you were to use an OR-IF 

    then you can do this - 

    IF(
        OR(
            BillingCity = "City1",
            BillingCity = "City2"
        ),
        "County 1",
        IF(
            OR(
                BillingCity = "City3",
                BillingCity = "City4"
            ),
            "County 2",
            "No Match"
        )
    )

     

    The syntax for IF is - 

    IF( expression, value_if_true, value_if_false )

     

    Now, the good thing is that you can nest them like I did - 

    IF( 
        expression, 
        IF( 
            expression, 
            value_if_true, 
            value_if_false
        ), 
        IF( 
            expression, 
            value_if_true, 
            value_if_false 
        ) 
    )

     

    where the value_if_true is just another IF( ... ).
撰写回答...
0/9000