15 Replies Latest reply: Nov 18, 2015 7:57 AM by Luca Jonathan Panetta RSS

    Multiple If Statements

      Hi All,

       

      Can someone please assist with the following load script. I am trying to get the 3 statuses loaded from the one If statement. What am I missing.

       

      If([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]=0,'New')
      and if([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'OOS')
      and if([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'Instock') as Status 

        • Re: Multiple If Statements
          Settu Periyasasamy

          Hi,

          If statement with or should be like [MRStatus]='ZP' or [MRStatus]='ZD' instead [MRStatus]='ZP'or'ZD'

          So, try the below


          If([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0,

            if([Qty]=0,'New',

                 if([Qty]>=1,'OOS',

                      if([Qty]>=1,'Instock','Others')))) as Status

           

          You can use the Match instead of Nested if Statement

            • Re: Multiple If Statements

              Settu,

               

              This creates the Statuses but they are not correct to the criteria. Also I made an error in the third one. Unrestrict should be >=1

               

              If([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]=0,'New')
              and if([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'OOS')
              and if([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]>=1 and [Qty]>=1,'Instock') as Status 

                • Re: Multiple If Statements
                  Settu Periyasasamy

                  Hi Kerry,

                  Try this..

                   

                  If([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0,

                    if( [Unrestrict]=0 and [Qty]=0,'New',

                         if([Unrestrict]=0 and [Qty]>=1,'OOS',

                              if([Unrestrict]>=1 and [Qty]>=1,'Instock', 'Deleted Item')))) as Status

                   

                  OR

                   

                  If([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]=0,'New',

                  if([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'OOS',

                  if([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]>=1 and [Qty]>=1,'Instock', 'Deleted Item'))) as Status

                    • Re: Multiple If Statements

                      Settu,

                       

                      The second one creates the status but it ignores the criteria. So for new and OOS I still get values when there should be no Unrestrict amount.

                        • Re: Multiple If Statements
                          Settu Periyasasamy

                          Hi,

                          Do you have the Null values in your Unrestrict Field, if so, you need to trim it.

                          Try the below expression..

                           

                          If([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and Len(Trim([Unrestrict]))>0 and [Qty]=0,'New',

                          if([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and Len(Trim([Unrestrict]))>0 and [Qty]>=1,'OOS',

                          if([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]>=1 and [Qty]>=1,'Instock', 'Deleted Item'))) as Status

                            • Re: Multiple If Statements

                              Settu,

                               

                              I have the If statements in 4 different fields in a report and they work ok see below. I just want these in the load as one statement.

                               

                              Field 1: =If(([MR Status]='ZP' or [MR Status]='ZD') AND Unrestrict-OpenQty<=0 and Unrestrict=0 and sum(Qty)=0,'New') 

                              Field 2: =If(([MR Status]='ZP' or [MR Status]='ZD') AND Unrestrict-OpenQty<=0 and Unrestrict=0 and sum(Qty)>=1,'OOS') 

                              Field 3: =If(([MRP Status]='ZP' or [MR Status]='ZD') AND sum(Unrestrict)>=1 and sum(Qty)>=1,'InStock') 

                              Field 4: =If(not([MR Status]='ZP' or [MR Status]='ZD'),'DeletedItem') 

                    • Re: Multiple If Statements
                      jagan mohan rao appala

                      Hi,

                       

                      Try like this

                       

                      If([MRStatus]='ZP' or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]=0,'New')

                      and if([MRStatus]='ZP'or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'OOS')

                      and if([MRStatus]='ZP'or [MRStatus]='ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'Instock') as Status

                       

                      OR

                       

                       

                      If(Match([MRStatus], 'ZP', 'ZD') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]=0,'New')

                      and if(Match([MRStatus], 'ZP', 'ZD') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'OOS')

                      and if(Match([MRStatus], 'ZP', 'ZD') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'Instock') as Status

                       

                      Also check your second and third if condition is same, so you will get only two values New & OOS only.

                       

                      Regards

                      Jagan.

                        • Re: Multiple If Statements

                          Jagan,

                           

                          These create the Statuses I want, but aren't correct to the criteria. Also the third item was incorrect should be as follows.

                          If([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]=0,'New')
                          and if([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'OOS')
                          and if([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]>=1 and [Qty]>=1,'Instock') as Status 

                           

                          I also need to added that if MRStatus is not ZP or ZD regardless of the other criteria it is a 'Deleted Item'

                            • Re: Multiple If Statements
                              jagan mohan rao appala

                              Try this

                               

                              If(Match([MRStatus], 'ZP', 'ZD') AND (Not Match([MRStatus], 'ZP', 'ZD') OR  [other criteria] = 'Deleted Item') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]=0,'New')

                              and if(Match([MRStatus], 'ZP', 'ZD') AND (Not Match([MRStatus], 'ZP', 'ZD') OR  [other criteria] = 'Deleted Item') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'OOS')

                              and if(Match([MRStatus], 'ZP', 'ZD') AND (Not Match([MRStatus], 'ZP', 'ZD') OR  [other criteria] = 'Deleted Item') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]>=1 and [Qty]>=1,'Instock') as Status

                               

                              Regards,

                              Jagan.

                              • Re: Multiple If Statements
                                Prashant Sangle

                                Hi,

                                 

                                You required little modification in Jagan's Solutions

                                 

                                Try below

                                 

                                If(Match([MRStatus], 'ZP', 'ZD') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]=0,'New',

                                if(Match([MRStatus], 'ZP', 'ZD') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'OOS',

                                if(Match([MRStatus], 'ZP', 'ZD') AND (Unrestrict-OpenQty)<=0 and [Unrestrict]>=1 and [Qty]>=1,'Instock','Deleted Item')))

                                 

                                Regards

                            • Re: Multiple If Statements
                              Massimo Grossi

                              you said

                              I have the If statements in 4 different fields in a report and they work ok see below. I just want these in the load as one statement.

                              Field 1: =If(([MR Status]='ZP' or [MR Status]='ZD') AND Unrestrict-OpenQty<=0 and Unrestrict=0 andsum(Qty)=0,'New')

                              Field 2: =If(([MR Status]='ZP' or [MR Status]='ZD') AND Unrestrict-OpenQty<=0 and Unrestrict=0 andsum(Qty)>=1,'OOS')

                              Field 3: =If(([MRP Status]='ZP' or [MR Status]='ZD') AND sum(Unrestrict)>=1 and sum(Qty)>=1,'InStock')

                              Field 4: =If(not([MR Status]='ZP' or [MR Status]='ZD'),'DeletedItem')

                               

                              if they work ok

                              you just need to remove the sum and add some "," and ")"

                               

                              if( (condition field 1 without sum), 'New',

                              if( (condition field 2 without sum), 'OOS',

                              if( (condition field 3 without sum), 'InStock',

                              if( (condition field 4 without sum), 'DeletedItem'

                              ))))

                              • Re: Multiple If Statements
                                Luca Jonathan Panetta

                                Hi Kerry,

                                 

                                your code:

                                If([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]=0,'New')
                                and if([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'OOS')
                                and if([MRStatus]='ZP'or'ZD' AND (Unrestrict-OpenQty)<=0 and [Unrestrict]=0 and [Qty]>=1,'Instock') as Status

                                 

                                my code:

                                If(Match([MRStatus], 'ZP', 'ZD') AND (Unrestrict-OpenQty) <= 0 AND Unrestrict = 0

                                    ,If([Qty]=0, 'New'

                                        ,If([Qty] >= 1, 'OOS'

                                            ,If([Qty]>=1, 'Instock')

                                ))) as Status

                                 

                                the result is exactly what you expect but I want you note [Qty]>=1

                                I think that it is incorrect because the last two conditions are the same but you want to show 2 different results (OSS, Instock).

                                 

                                Best Regards

                                Luca Jonathan Panetta