Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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))
)
Hi gwassenaar,
Thanks for your response.
Is there a way to do this within the script?
Alan
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))
)
HI
if (IsNull(PercentComp)
,sum(if(CategoryDescription='Financial',1,0))
,sum(if(CategoryDescription='Financial', 1-(PercentComp/100), NULL))
)
Hope it helps
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
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))
)
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.
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