Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

8 Replies
Gysbert_Wassenaar

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
Author

Hi gwassenaar,

Thanks for your response.

Is there a way to do this within the script?

Alan

Not applicable
Author

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

)

MayilVahanan

HI

if (IsNull(PercentComp)

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

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

)

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

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
Author

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

MayilVahanan

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

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