Forum Discussion

gpiero's avatar
gpiero
Skilled Sharer
9 years ago
Solved

Operators AND - OR in Nested IF

I am struggling with a silly formula but I am not able to find the proper solution. I know it should be easy .....

 

CS_Universe = 
IF (
    ZV[OrgCom] = "CUSTORG"
        && ZV[IDCust] = "491"
        && ( ZV[IDDel] = "SPD99"
        || ZV[IDDel] = "WAR99"
        || ZV[IDDel] = "DPD99"
        || ZV[IDDel] = "FOC99"
        || ZV[IDDel] = "KBD99" );
    "1CS";
    IF (
        ZV[OrgCom] = "CUSTORG"
            && ZV[IDCust] = "491"
            && (ZV[CounFinGood] = "MX"   
            || ZV[CounFinGood] = "RU"
            || ZV[CounFinGood] = "AR"
            || ZV[CounFinGood] = "CN")
            ;
        "2CS";
        IF (
            [OrgCom] = "CUSTORG"
                && [IDCust] <> "491"
                && [ECC] = "X"
                && ( ZV[IDDel] = "SPD99"
                || ZV[IDDel] = "WAR99"
                || ZV[IDDel] = "DPD99"
                || ZV[IDDel] = "FOC9"
                || ZV[IDDel] = "KBD99" );
            "3CS";
            IF (
                [OrgCom] = "CUSTORG"
                    && [IDCust] <> "491"
                    && [ECC] = BLANK ()
                    && ( ZV[IDDel] = "SPD99"
                    || ZV[IDDel] = "WAR99"
                    || ZV[IDDel] = "DPD99"
                    || ZV[IDDel] = "FOC99"
                    || ZV[IDDel] = "KBD99" );
                "4CS";
                BLANK ()
            )
        )
    )
)

This formula work perfectly except the 2nd if that should write "2CS".  I also moved it bottom just to test.

 

Then I added a new calculated column putting only the following and I have got the right result.

IF (
        ZV[OrgCom] = "CUSTORG"
            && ZV[IDCust] = "491"
            && (ZV[CounFinGood] = "MX"   
            || ZV[CounFinGood] = "RU"
            || ZV[CounFinGood] = "AR"
            || ZV[CounFinGood] = "CN")
            ;
        "2CS";

 

So syntax is correct and the order of operators too, otherwise the first IF above should not work.

Please is there someone who could explain me how to fix it?

Thanks

  • Framet

    Many thanks for your advices.

    I tried to split in 4 different columns. Doing  that I realized that 1st IF should have a differente logic:

     

    IF (
        ZV[OrgCom] = "CUSTORG"
            && ZV[IDCust] = "491"
            && ( ZV[IDDel] = "SPD99"
            || ZV[IDDel] = "WAR99"
            || ZV[IDDel] = "DPD99"
            || ZV[IDDel] = "FOC99"
            || ZV[IDDel] = "KBD99" )
            && (ZV[CounFinGood] <> "MX"   
            && ZV[CounFinGood] <> "RU"
            && ZV[CounFinGood] <> "AR"
            && ZV[CounFinGood] <> "CN");
    
        "1CS";

     

     

    Now I have changed the orginal long formula and  it is ok.

     

    Thanks again.

8 Replies

  • Without going into the what and why of this query. Can you advise on the returned value for SC_Universe you are seeing when you believe the second nested IF should be catching the result? Eg when you are expecting "2CS".

    Kind Regards

     

    Thomas

     

     

    • gpiero's avatar
      gpiero
      Skilled Sharer

      I am not sure to caught your point. Do you mean which condition should be satisfied?

      • Framet's avatar
        Framet
        Resolver II

        You mention the formula works perfectly except the second IF. I'm assuming at somepoint you are expecting the result  "2CS" but getting something else. What is that something else?