topic Re: if condition in QlikView

if condition

bharatkishore — Fri, 18 May 2018 13:56:33 GMT

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

Re: if condition

olivierrobin — Fri, 18 May 2018 14:01:18 GMT

hello

what does not working mean ?

error ?

wrong result ?

Re: if condition

YoussefBelloum — Fri, 18 May 2018 14:03:11 GMT

Hi,

did you add a GROUP BY at the end of the LOAD ?

Re: if condition

bharatkishore — Fri, 18 May 2018 14:03:18 GMT

I am getting as invalid expression

Re: if condition

bharatkishore — Fri, 18 May 2018 14:03:54 GMT

Yes i have added group by..

Re: if condition

m_perreault — Fri, 18 May 2018 14:05:35 GMT

Take -1 out of parenthesis

Re: if condition

bharatkishore — Fri, 18 May 2018 14:05:47 GMT

Please find my code so that it will be easy for you.

Re: if condition

bharatkishore — Fri, 18 May 2018 14:07:17 GMT

Hi,

Tried by removing -1 but still same error

Re: if condition

YoussefBelloum — Fri, 18 May 2018 14:07:54 GMT

what are you trying to calculate using this:

sum(mat_con_qua)*(-1)*sum([U-D_NL9s])*1.055

why multiplying by -1 and 1.055 ?

Re: if condition

olivierrobin — Fri, 18 May 2018 14:07:59 GMT

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

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

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

Re: if condition

bharatkishore — Fri, 18 May 2018 14:08:46 GMT

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;

Re: if condition

olivierrobin — Fri, 18 May 2018 14:10:34 GMT

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)

Re: if condition

bharatkishore — Fri, 18 May 2018 14:11:08 GMT

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

Re: if condition

olivierrobin — Fri, 18 May 2018 14:13:38 GMT

see my answer below

Re: if condition

bharatkishore — Fri, 18 May 2018 14:15:51 GMT

Sorry getting error with your expression

Re: if condition

vishsaggi — Fri, 18 May 2018 14:20:03 GMT

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

Re: if condition

Peter_Cammaert — Fri, 18 May 2018 14:20:23 GMT

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.

Re: if condition

olivierrobin — Fri, 18 May 2018 14:21:04 GMT

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

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

)

Re: if condition

bharatkishore — Fri, 18 May 2018 14:22:06 GMT

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

Re: if condition

Peter_Cammaert — Fri, 18 May 2018 14:23:38 GMT

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.