Operators AND - OR in Nested IF

[gpiero]

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]

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]

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

[Framet]

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?

[gpiero]

I get nothing.

While using the calculation of the 2nd column added for test COUNTROWS finds 91 rows and tha value is ok due I checked it via ERP.

[Framet]

Whilst the second column you mention the formula is incomplete if we assume it simply returns blank when not "2CS" then I'm with you in so far as I can't see how that can work yet the first calculated column believes the value isn't a match and falls out as blank().

I also agree the structure of the formula whilst not idea should achieve what you describe you want.

Have you checked an individual row in the table to see what the first column returns for a record that matches:

[OrgCom] = "CUSTORG"
            && ZV[IDCust] = "491"
            && ZV[CounFinGood] = "MX"   

 You could of course create 4 columns, one for each IF statement and a 5th column that concatonates the result. (assuming no record can match more than one IF statement, if the order is important then simply create the 5th column to return the first when not blank else the second and so on. 

Not efficient in terms of storage but should have the same effect.

[gpiero]

Sorry,

I have copied it partially.

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

[gpiero · Accepted solution]

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

[v-yuezhe-msft]

Hi @gpiero,

From your description,  you have solved this issue, right? If that is the case, you can accept  your reply as solution, that way, other community members would benefit from your solution.

Thanks,
Lydia Zhang