topic Re: Logic challenge in QlikView

Logic challenge

Fri, 07 Sep 2012 14:36:27 GMT

Hi All,

I require some help regarding a logic challenge please....

Up to now I have used simple IF logic statements. I now need to add some (slightly) more detailed logic into my QV app which in simple terms will do the following:

Check 'PercentComp' field to see if a value exists or is null
If a value exists (i.e. we have a entry on the system), then sum the records that meet certain criteria e.g. sum(if(CategoryDescription='Financial' and Due_Date>today(2), 1-(PercentComp/100), NULL))
If a value does not exist then count the records that meet certain criteria e.g. count(if(status='open' and Due_Date>today(2) and CategoryDescription='Financial'

The above fields reside in two tables:

Table 1 = PercentComp

Table 2 = CategoryDescription, Due_Date,status

I am looking for guidance as to the best way to do this. I would ideally like to handle most of the logic within the script as I am getting 'Out of Object Memory' quite a bit.

Many thanks,

Alan

Re: Logic challenge

Gysbert_Wassenaar — Fri, 07 Sep 2012 14:40:47 GMT

something like this should do the trick:

if (IsNull(PercentComp)

,sum(if(status='open' and Due_Date>today(2) and CategoryDescription='Financial'),1,0)

,sum(if(CategoryDescription='Financial' and Due_Date>today(2), 1-(PercentComp/100), NULL))

)

Re: Logic challenge

Fri, 07 Sep 2012 15:34:38 GMT

Hi gwassenaar,

Thanks for your response.

Is there a way to do this within the script?

Alan

Re: Logic challenge

Fri, 07 Sep 2012 16:47:13 GMT

I am getting null returns when I use the IF statement that you kindly prepared for me. The syntax looks correct and I have even stripped it back for troubleshooting purposes however it is still the same - see code below.

There are plenty of records that meet this criteria. Even if there werent't I should be getting 0 rather than null.

Any ideas please?

Thanks

Alan

if (IsNull(PercentComp)

,sum(if(CategoryDescription='Financial'),1,0)

,sum(if(CategoryDescription='Financial', 1-(PercentComp/100), NULL))

)

Re: Logic challenge

MayilVahanan — Fri, 07 Sep 2012 16:53:16 GMT

if (IsNull(PercentComp)

,sum(if(CategoryDescription='Financial',1,0))

,sum(if(CategoryDescription='Financial', 1-(PercentComp/100), NULL))

)

Hope it helps

Re: Logic challenge

Fri, 07 Sep 2012 17:27:49 GMT

Hi Mayil,

Thanks for your help.

It appears to work, however it has caused an 'Out of object memory' error!

What can I do to resolve this?

Alan

Re: Logic challenge

Fri, 07 Sep 2012 18:12:23 GMT

Ok - I have resolved the out of memory issue, however my results are still 'null'.

Help please?!

if (IsNull(PercentComp)

,sum(if(CategoryDesc='Financial' and Open_Clos='Closed',1,0))
,sum(if(CategoryDesc='Financial' and Open_Clos='Closed' and PercentCompletion<100, RemainingPercentage), NULL))
)

Re: Logic challenge

MayilVahanan — Fri, 07 Sep 2012 18:44:23 GMT

Try like this,

if (IsNull(PercentComp),sum(if(CategoryDesc='Financial' and Open_Clos='Closed',1,0)) ,sum(if(CategoryDesc='Financial' and Open_Clos='Closed' and (PercentCompletion <100), RemainingPercentage, 'NULL'))
)

I had some other work, so its take time to reply..

Hope it helps..

Re: Logic challenge

Mon, 10 Sep 2012 12:12:35 GMT

Thanks.

I think the reason that I am getting null is due to the first IF statement. The field 'PercentComp' will not exist in all cases. I presume this is treated different lt to a null?

What is the best way to evaluate the existance of a field for not?

Thanks

Alan