Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Logic challenge

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:

  1. Check 'PercentComp' field to see if a value exists or is null
  2. 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))
  3. 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

Tags (4)
8 Replies

Re: Logic challenge

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

    )


talk is cheap, supply exceeds demand
Not applicable

Re: Logic challenge

Hi gwassenaar,

Thanks for your response.

Is there a way to do this within the script?

Alan

Not applicable

Re: Logic challenge

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

HI

if (IsNull(PercentComp)

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

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

)

Hope it helps

Not applicable

Re: Logic challenge

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

Not applicable

Re: Logic challenge

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

HI

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

Not applicable

Re: Logic challenge

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

Community Browser