Problem with load script

[#1]

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?

[#2]

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

[#3]

I could give you the complete code?

[MK_QSL]

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

[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!

[#6]

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?

[MK_QSL]

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"

[sasiparupudi1]

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