16 Replies Latest reply: Jul 17, 2015 8:04 AM by Sasidhar Parupudi RSS

    Problem with load script

    Andre Avenant

      Hello

       

      Me again, i am not comming right with this:

       

      if([Sale Finalized]='Yes' , if(round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01'  OR
      round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling'),
      if([Sale Finalized]='No' ,
      if("SubSale Deal Type Text" = 'Fixed', 'Fixed',
      if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'Consignment' , 'Consignment'),
      if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'Fixed',
      if(round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01'  OR
      round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling'),
      if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'MGP', round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01'  OR
      round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling'))))) as "Variance Report"

       

      What am i not seeing?

        • Re: Problem with load script
          Manish Kachhia

          Your If Condition Mismatching !

          Look into below

          if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'MGP', round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01'  OR
          round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling')))))


          IF(something then round(something) or Round(something)) this is wrong


          Can you provide your full logic, I will create full if statement for you.

          • Re: Problem with load script
            Avinash R

            I didn't get whats the issue is can you please elaborate a bit more on what is happening and what your try to achieve

            • Re: Problem with load script
              kushal chawda

              if([Sale Finalized]='Yes' , if(round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01'  OR

              round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling',

              if([Sale Finalized]='No' ,

              if("SubSale Deal Type Text" = 'Fixed', 'Fixed',

              if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'Consignment' , 'Consignment',

              if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'Fixed',

              if(round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01'  OR

              round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling',

              if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'MGP', round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01'  OR

              round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling')))))))) as "Variance Report"

                • Re: Problem with load script
                  Andre Avenant

                  mmm, okay, byt the as "Variance report" is still not blue, meaning the code is not correct yet

                    • Re: Problem with load script
                      Manish Kachhia

                      Can you provide some information on your If statement.

                       

                      Something like

                      Sale Finalized = 'Yes' then

                       

                       

                      Sales Finalized = 'No' then

                       

                      etc etc

                        • Re: Problem with load script
                          Andre Avenant

                          okay, what detail do you require, as i don't know who else to give the statement thru. I just think there is a issue with :

                          if([Sale Finalized]='Yes' , if(round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01'  OR
                          round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling'),
                          if([Sale Finalized]='No' ,
                          if("SubSale Deal Type Text" = 'Fixed', 'Fixed',
                          if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'Consignment' , 'Consignment'),

                           

                          the Reconciling and Consigment falses are not allowing the code to run through

                          • Re: Problem with load script
                            Andre Avenant

                            I could give you the complete code?

                              • Re: Problem with load script
                                Manish Kachhia

                                We don't need Code.. We need to understand the logic behind your nested if sentence !

                                  • Re: Problem with load script
                                    Andre Avenant

                                    okay, so we are trying it to say that is [Sale Finalized]='Yes'  , then have a look at the sum in the call and if the values are in line as set in the code then display 'Difference', else display 'Reconciling'.

                                     

                                    but

                                     

                                    if [Sale Finalized]='No' , and SubSale Deal Type Text" = 'Fixed the display the outcome as Fixed...right track?

                                      • Re: Problem with load script
                                        Manish Kachhia

                                        if([Sale Finalized]='Yes' ,

                                             if(round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01'  OR
                                             round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling'),


                                        Yes portion seems to be OK


                                        if([Sale Finalized]='No' ,
                                             if("SubSale Deal Type Text" = 'Fixed', 'Fixed',
                                             if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'Consignment' , 'Consignment'),
                                             if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'Fixed',


                                        No portion is also OK


                                        if(round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01'  OR

                                             round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling'),


                                        Again in No portion you are checking the same as yes portion



                                        But what about below ????? This is very difficult to understand.

                                        if("SubSale Deal Type Text" = 'Consignment' and "Sale Deal Type Text" = 'MGP', round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01'  OR
                                        round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling'))))) as"Variance Report"

                              • Re: Problem with load script
                                Andre Avenant

                                what changed?

                                  • Re: Problem with load script
                                    Sasidhar Parupudi

                                    Please try

                                    if([Sale Finalized]='Yes',

                                    if(Round(([Return to Grower (GROSS)] - [Return to Grower X]),0.01) < '-0.01' OR round(([Return to Grower (GROSS)] - [Return to Grower X]),0.01)>'0.01', 'Difference', 'Reconciling')

                                    if([Sale Finalized]='No' ,

                                    if([SubSale Deal Type Text] = 'Fixed', 'Fixed',

                                    if([SubSale Deal Type Text] = 'Consignment' and [Sale Deal Type Text] = 'Consignment' , 'Consignment',

                                    if([SubSale Deal Type Text] = 'Consignment' and [Sale Deal Type Text] = 'Fixed',

                                    if(round(([Return to Grower (GROSS)] - [Return to Grower X]),0.01) < '-0.01'  OR

                                    round(([Return to Grower (GROSS)] - [Return to Grower X]),0.01)>'0.01', 'Difference', 'Reconciling',

                                    if([SubSale Deal Type Text] = 'Consignment' and [Sale Deal Type Text] = 'MGP', round(([Return to Grower (GROSS)] - [Return to Grower X]),0.01) < '-0.01'  OR

                                    round(([Return to Grower (GROSS)] - [Return to Grower X]),0.01)>'0.01', 'Difference', 'Reconciling'))))))) as "Variance Report"

                                • Re: Problem with load script
                                  Michalina Kuczynska

                                  As the rest of the guys mentioned that before - I'm not sure what is that you're trying to achieve here. I don't think your IF statement is nested properly - the best way to check it is probably to draw a block diagram on the paper and try some code again. Manish mentioned above:

                                   

                                  IF(something then round(something) or Round(something)) this is wrong

                                   

                                  and I think he is right. One of the ways to achieve the result of logical ORs and ANDs using the IF statement would be probably to nest more and more IF functions into your code. I think you can also think about using the FABS() function to get the absolute value for the below calculation:


                                  FABS(Round(([Return to Grower (GROSS)] - [Return to Grower X]),0.01)) > 0.01


                                  which allow you to simplify at least one part of the IF statement. Try to replace them all using fabs().

                                   

                                  You are combining few dimensions at once - maybe it would be better to split it into smaller chunks, create separate flags in your load script and than try to organize some additional mapping using the MATCH() function in preceding load?

                                   

                                  Remember the basics - IF(expression, then, else )

                                  From my understanding the code below:

                                   

                                  if([Sale Finalized]='Yes' , if(round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01'  OR
                                  round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling')

                                   

                                  seems to be a full IF statement straight away - where you have:

                                  expression >>> [Sale Finalized]='Yes'

                                       expression >>> (nested IF) IF(FABS(Round(([Return to Grower (GROSS)] - [Return to Grower X]),0.01)) > 0.01)

                                            then >>> 'Difference'

                                                 else >>> 'Reconciling'

                                  (end)

                                   

                                  When nesting IF statements try to always start from the biggest, more "global" conditions, going down and down into to lowest level of detail.

                                  Good luck!

                                  • Re: Problem with load script
                                    Sasidhar Parupudi

                                    Please try

                                    if([Sale Finalized]='Yes' ,

                                    if(round([Return to Grower (GROSS)]-[Return to Grower X] ,0.01) < '-0.01'  OR round([Return to Grower (GROSS)]  - [Return to Grower X] ,0.01)>'0.01', 'Difference', 'Reconciling'),

                                    if([Sale Finalized]='No' ,

                                    if([SubSale Deal Type Text] = 'Fixed', 'Fixed',

                                    if([SubSale Deal Type Text] = 'Consignment' and "Sale Deal Type Text" = 'Consignment' , 'Consignment'),

                                    if([SubSale Deal Type Text] = 'Consignment' and "Sale Deal Type Text" = 'Fixed',

                                    if(round([Return to Grower (GROSS)] - [Return to Grower X],0.01) < '-0.01'  OR

                                    round([Return to Grower (GROSS)] - [Return to Grower X],0.01)>'0.01', 'Difference', 'Reconciling'),

                                    if([SubSale Deal Type Text] = 'Consignment' and [Sale Deal Type Text] = 'MGP', if(round([Return to Grower (GROSS)] - [Return to Grower X],0.01) < '-0.01'  OR

                                    round([Return to Grower (GROSS)] - [Return to Grower X],0.01)>'0.01', 'Difference', 'Reconciling')))))) as "Variance Report"

                                     

                                     

                                     

                                    I think you are missing an if

                                    round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01) < '-0.01'  OR
                                    round(("Return to Grower (GROSS)" - "Return to Grower X"),0.01)>'0.01', 'Difference', 'Reconciling'))))) as "Variance Report"


                                    hope this helps