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 Periyasamy

          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 Periyasamy

                  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 Periyasamy

                          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
                                  Settu Periyasamy

                                  Hi Kerry,

                                  check the below one..

                                   

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

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

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

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

                                   

                                  If it is not working, Can you Post the sample qvw?

                      • 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