IF statement changed to set analysis

[Anonymous]

Hi guys,

I have an expression which takes ages to calculate and 9 times out of 10 even times out "Calculation timed out"...

The expression is this

sum({<pricing_invoice_dated_year={$(vyear1)}>} if(pricing_invoice_CustNo = pricing_CustNo,pricing_invoice_val))

Can this be changed to set analysis somehow to speed this up?

Thanks

[Kushal_Chawda]

try this,

sum({<pricing_invoice_dated_year={'$(=vyear1)'},pricing_invoice_CustNo={"=pricing_invoice_CustNo=pricing_CustNo"}>} pricing_invoice_val)

[ogster1974]

Try this

sum({<pricing_invoice_dated_year={$(vyear1)}, pricing_invoice_CustNo=pricing_CustNo>} pricing_invoice_val)

[sunny_talwar]

Is there any chance that pricing_invoice_CustNo and pricing_CustNo are getting accessed from two different tables in the application? If that is the case, I don't think Set Analysis will be much helpful.

Best,

Sunny

[#5]

Hi Jamel,

the best way to do this I would suggest, is to create a flag field in your script which you can then use in your set analysis expression.

e.g

load

if(pricing_invoice_CustNo = pricing_CustNo ,1) As CustNumberMatchFlag

from ......

(your load script here)

then your expression becomes

sum({<pricing_invoice_dated_year={$(vyear1)}, CustNumberMatchFlag={1} >} pricing_invoice_val)

hope that helps

Joe

[Anonymous]

Hi Sunny

Yes they are from two different tables.  I can't join them as I create a loop in my structure so I'm having to get around this by using this IF statement..

[sunny_talwar]

To tell you the truth, I know this is an issue when you have fields from two different tables (which can cause a Cartesian product), but I have never been able to handle it. Always had to look for a work around. I won't be able to recommend a a work around before looking at the application, but since I don't have a licensed version of QlikView I won't be able to open if you share something.

But I am sure other experts will def. be able to help you out.

Best,

Sunny

[Anonymous]

Thanks Sunny.  If anyone else can offer any advice/suggestions that would be brilliant!

Thanks guys

[Anonymous]

Tried this expression but I am getting a syntax issue (screenshot below)

[Anonymous]

Hi Jamal,

I would suggest you try:

if(pricing_invoice_CustNo = pricing_CustNo,pricing_invoice_val),sum({<pricing_invoice_dated_year={$(vyear1)}>}),0) and if there is still the same issue, I would suggest you use Joe Simmons suggestion of having a flag.

It is always best to let the back-end script do all the heavy lifting calculations for you instead of set analysis as having huge calculations slows down the application