if condition

[bharatkishore]

Hi All,

I am writing the below expression in script.. but my expression is not working..Can you please tell me where i am missing out..

if(Country='Brazil' and Batch_Code='U',

if(Strategic_Part='Tubes' and [U-D_NL9s]<>0 ,sum(mat_con_qua)*(-1)*sum([U-D_NL9s])*1.055,0)) as test

Thanks,

Bharat

[bharatkishore]

Sorry,

please find the code below:

LOAD

     Strategic_Part,

     Calendar_Month_Code,

     Country,

     Batch_Code,

if(Country='Brazil' and Batch_Code='U',

if(Strategic_Part='Tubes' and [U-D_NL9s]<>0 ,sum(mat_con_qua)*-1*sum([U-D_NL9s])*1.055,0)) as test    

from [..\5_QVD\NR_QVDs\Consfiltered_NR.qvd]

(qvd)group by  Batch_Code, Country,Strategic_Part,Calendar_Month_Code;

[olivierrobin]

in you are in a load and want to do a sum, I would write

load .....

sum(

if(Country='Brazil' and Batch_Code='U',

if(Strategic_Part='Tubes' and [U-D_NL9s]<>0 ,mat_con_qua)*(-1)*U-D_NL9s])*1.055,0)) as test

group by ......

because with you syntax, you mix row level treatement (the if clause) and group treatmenet (sum)

[bharatkishore]

Those are two values i need to use for my calculation.-1 and 1.055

first i need to multiple sum(mat_con_qua)*-1 and with that result i need to multiply

sum(sum([U-D_NL9s])

and with that result i need to multiply

1.055

[olivierrobin]

see my answer below

[bharatkishore]

Sorry getting error with your expression

[vishsaggi · Accepted solution]

Try this

     if(Country='Brazil' and Batch_Code='U',

     (Sum(if(Strategic_Part='Tubes' and [U-D_NL9s]<>0, mat_con_qua))*-1)*(sum([U-D_NL9s])*1.055),0) as test   

[Peter_Cammaert]

Field [U-D_NL9s] is missing from your GROUP BY clause. Add it at the end of the GROUP BY list, and I think it will be ok.

[olivierrobin]

sum(

if(Country='Brazil' and Batch_Code='U',

                            if(Strategic_Part='Tubes' and [U-D_NL9s]<>0 ,

                                    mat_con_qua*(-1)*U-D_NL9s]

                                   ,0

                               )*1.055,0)

) as test

i didn't check the number of right anf left parenthesis .....

and the question is : which value do you want if your conditions are false ?

so, you may want to write

sum( if (Country='Brazil' and Batch_Code='U' and Strategic_Part='Tubes' and [U-D_NL9s]<>0,

              mat_con_qua*(-1)*U-D_NL9s*1.055

            ),

       insert here the value in case the test is false

)

[bharatkishore]

That is a calculated field.. so i cannot use it as group by [U-D_NL9s]

[Peter_Cammaert]

Not yet. Either add [U-D_NL9s] to the GROUP BY list and to the column list in your LOAD statement, or (and this is the better solution I think) replace [U-D_NL9s]<>0 with Sum([U-D_NL9s]) <> 0 in your IF() expression.