topic Re: IF statement changed to set analysis in QlikView

IF statement changed to set analysis

Anonymous — Fri, 07 Aug 2015 07:51:12 GMT

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

Re: IF statement changed to set analysis

Kushal_Chawda — Fri, 07 Aug 2015 07:54:36 GMT

try this,

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

Re: IF statement changed to set analysis

ogster1974 — Fri, 07 Aug 2015 07:56:59 GMT

Try this

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

Re: IF statement changed to set analysis

sunny_talwar — Fri, 07 Aug 2015 07:58:04 GMT

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

Re: IF statement changed to set analysis

Fri, 07 Aug 2015 08:02:46 GMT

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

Re: IF statement changed to set analysis

Anonymous — Fri, 07 Aug 2015 08:12:42 GMT

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..

Re: IF statement changed to set analysis

sunny_talwar — Fri, 07 Aug 2015 08:19:34 GMT

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

Re: IF statement changed to set analysis

Anonymous — Fri, 07 Aug 2015 09:11:42 GMT

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

Thanks guys

Re: IF statement changed to set analysis

Anonymous — Fri, 07 Aug 2015 09:19:16 GMT

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

Re: IF statement changed to set analysis

Anonymous — Fri, 07 Aug 2015 09:20:13 GMT

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

Re: IF statement changed to set analysis

Anonymous — Fri, 07 Aug 2015 09:34:56 GMT

The problem is I can't do it in the load script because the fields pricing_invoice_CustNo and pricing_CustNo are from different tables.

Unless I am wrong here and you can do it?

Re: IF statement changed to set analysis

sunny_talwar — Fri, 07 Aug 2015 09:37:05 GMT

It can be done, but again you run into the issue of creating a Cartesian Product between two tables. You can explore the option of using Mapping Load where you bring only one field from one table into the other without worrying about the Cartesian product

Re: IF statement changed to set analysis

Anonymous — Fri, 07 Aug 2015 09:37:48 GMT

I am getting a syntax issue on this expression as well?

Re: IF statement changed to set analysis

Kushal_Chawda — Fri, 07 Aug 2015 09:42:34 GMT

Mel_Mesaoud have you tried what i had suggested?

Re: IF statement changed to set analysis

Anonymous — Fri, 07 Aug 2015 09:46:22 GMT

Hi Kush,

I did try it but the value is zero

Re: IF statement changed to set analysis

ogster1974 — Fri, 07 Aug 2015 10:00:02 GMT

Try

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

I use this approach when wiring my master calendar up to various dates within my model. It seems to work for me although your use case may be different.

Perhaps need to AGGR your data aswell to bring back what you want.

AGGR(SUM({<pricing_invoice_dated_year={$(vyear1)}, pricing_invoice_CustNo=p(pricing_CustNo)>} pricing_invoice_val) pricing_invoice_CustNo)

Re: IF statement changed to set analysis

sasiparupudi1 — Fri, 07 Aug 2015 10:45:33 GMT

do you get the results quickly if you just did sum({<pricing_invoice_dated_year={$(vyear1)}>}

Re: IF statement changed to set analysis

sasiparupudi1 — Mon, 10 Aug 2015 20:10:20 GMT

Have you managed to solve your problem?

Sasi

Re: IF statement changed to set analysis

qlikviewwizard — Tue, 11 Aug 2015 01:28:01 GMT

HI Mel_Mesaoud

Can you attach the sample file to demonstrate your issue? Thank you.