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" ...
Need Help
  • gpiero's avatar
    gpiero
    9 years ago

    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.

Framet's avatar
Framet
Resolver II
9 years ago

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
9 years ago

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

  • Framet's avatar
    Framet
    Resolver II
    9 years ago

    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's avatar
      gpiero
      Skilled Sharer
      9 years ago

      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's avatar
        Framet
        Resolver II
        9 years ago

        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.