Set Analysis query

[rahulhv1]

how can i write set analysis query for

  =if(isnull(FinanceRelease)=-1 and isnull(BusinessHeadRelease)=0 ,count(CapexKey))

here financerelease & businessheadrelease are date fields.

Pls. help me out for this.

[jonathandienst]

Hi

Just like you cannot select a null value from a list box, or chart, you cannot select a null value using a set expression - which is, after all, just a way of automating and combining manual selections.

Your options are to create a flag field  as suggested above, or to use:

NullAsValue FinanceRelease;

before loading, so that the null values are turned into an empty string. These can be selected manually and in set expressions.

HTH

Jonathan

[simenkg]

You cant specify null() like this in set analysis.

What you should do is make flags in your script

load *,

if(isnull(FinanceRelease) and not(isnull(BusinessHeadRelease)),1,0) as CountFlag,

.

.

.

Then your expression becomes

count({$<CountFlag={1}>} CapexKey)

Hope this helps

Regards
SKG

[#4]

Simen, just as a point I would probably create the flag on the one field rather than both, it just gives you a bit more flexibility.

For instance if a new requirement to show just null FinanceRelease and all BusinessHeadRelease(including nulls) or another field was needed, you wouldn't be able to use that flag, on the one field though you could do both

Joe

[simenkg]

I agree and would do it myself. This was just to illustrate the approach.

load *,

if(isnull(FinanceRelease) ,1,0) as FinanceReleaseNullFlag,

if(not(isnull(BusinessHeadRelease)),1,0) as BusinessHeadReleaseValidFlag,

.

.

.

Then your expression becomes

count({$<FinanceReleaseNullFlag={1},BusinessHeadReleaseValidFlag={1}>} CapexKey)

[#6]

Yea of course, glad we were thinking along the same lines!

[rahulhv1]

Thanks a lot, I did the same thing, i load statement, i have  written below codes and it worked very well.

if (isnull(FinanceRelease2),0, FinanceRelease2) as FinanceRelease,
if (isnull(PlantHeadRelease2),0, PlantHeadRelease2) as PlantHeadRelease,
if (isnull(BusinessHeadRelease2),0, BusinessHeadRelease2) as BusinessHeadRelease,
if (isnull(ITRelease2),0, ITRelease2) as ITRelease,
if (isnull(PresidentRelease2),0, PresidentRelease2) as PresidentRelease