Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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?
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
Well, hope you get well soon. Almost X-mas...