topic Re: Problem with if condition in QlikView

Problem with if condition

geogou1973 — Thu, 17 Dec 2015 19:08:44 GMT

Hello.

Can anyone tell me how can i convert the following expression into set analysis because i have a lot of data and i have problem with the performance.

sum(if(FORMAT='FRANC' and SALES_DATE='$(PREVIOUS_UDATE)',AMOUNT_TRIG,

if(SALES_DATE='$(PREVIOUS_UDATE)',SALES_AMT_FPA)))

Thank you in advance.

Re: Problem with if condition

marcus_sommer — Fri, 18 Dec 2015 03:00:44 GMT

For single-fileds calculations you could change it into:

sum({> FORMAT = {'FRANC'}, SALES_DATE = {'$(PREVIOUS_UDATE)'} >} AMOUNT_TRIG)

but this won't be applicable if you want to aggregate different fields depending on your conditions. But you could optimize the calculation by replacing the sum(if to if(sum, like:

if(FORMAT='FRANC' and SALES_DATE='$(PREVIOUS_UDATE), sum(AMOUNT_TRIG),

if(SALES_DATE='$(PREVIOUS_UDATE)',sum(SALES_AMT_FPA))

Re: Sum(if(...)) vs If(Sum(...), Sum(...))

- Marcus

Re: Problem with if condition

Peter_Cammaert — Fri, 18 Dec 2015 10:25:45 GMT

How about this?

sum({<FORMAT={'FRANC'}, SALES_DATE={'$(PREVIOUS_UDATE)'}>} AMOUNT_TRIG) +

sum({<FORMAT-={'FRANC'}, SALES_DATE={'$(PREVIOUS_UDATE)'}>} SALES_AMT_FPA)

Best,

Peter

PS. Marcus, do you ever sleep?

Re: Problem with if condition

marcus_sommer — Fri, 18 Dec 2015 10:37:01 GMT

Not so well these days. I have caught a cold and after a coughing attack I'm awake ... and then I think I could do anything useful ...

- Marcus

Re: Problem with if condition

Peter_Cammaert — Fri, 18 Dec 2015 11:35:41 GMT

Well, hope you get well soon. Almost X-mas...