topic Re: Set Analysis query in QlikView

Set Analysis query

rahulhv1 — Fri, 01 Aug 2014 09:05:34 GMT

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.

Re: Set Analysis query

Anonymous — Fri, 01 Aug 2014 09:11:06 GMT

Something like below,

=Count({$<FinanceRelease={"=isNull(FinanceRelease)"}, BusinessHeadRelease={"*"}>} CapexKey)

Re: Set Analysis query

Fri, 01 Aug 2014 09:12:29 GMT

Hi Rahul

try this

count({<FinanceRelease={'isnull(FinanceRelease)=-1'},BusinessHeadRelease={'isnull(BusinessHeadRelease)=0'}>}CapexKey)

hope this will help you

Re: Set Analysis query

MK_QSL — Fri, 01 Aug 2014 09:17:25 GMT

May be something like this..

Count({$<FinanceRelease = {"=LEN(TRIM(FinanceRelease))=0"},BusinessHeadRelease = {"=LEN(TRIM(BusinessHeadRelease))>0"}>}CapexKey)

Re: Set Analysis query

rahulhv1 — Fri, 01 Aug 2014 09:36:28 GMT

Sorry ,but its not giving any results

Re: Set Analysis query

rahulhv1 — Fri, 01 Aug 2014 09:37:42 GMT

Hi, Nirmal

Thanks for your reply, but sorry to say,its not working nor giving any errors

Re: Set Analysis query

MayilVahanan — Fri, 01 Aug 2014 09:40:54 GMT

Try like this

=Count({<FinanceRelease-={"*"}, BusinessHeadRelease={"*"}>} CapexKey)

Re: Set Analysis query

perumal_41 — Fri, 01 Aug 2014 10:10:51 GMT

Try Like this

count(CapexKey={"=isnull(FinanceRelease)=-1 and isnull(BusinessHeadRelease)=0"}>}CapexKey))

Re: Set Analysis query

Fri, 01 Aug 2014 10:31:41 GMT

Hi Rahul,

probably the simplest way to do this is to create a null flag field in your script and use that within the set analysis.

In your script:

If(Isnull(FinanceRelease),'NULL') As FinanceRelease_NullFlag

Then in your expression:

=Count({$<FinanceRelease_NullFlag={'NULL'}, BusinessHeadRelease={'*'}>} CapexKey)

Hope that helps

Joe

Re: Set Analysis query

Fri, 01 Aug 2014 10:44:23 GMT

if(<FinanceRelease={'$(=null())'} ,BusinessHeadRelease={'$(=null())'}> count(CapexKey))

Re: Set Analysis query

jonathandienst — Fri, 01 Aug 2014 10:44:39 GMT

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

Re: Set Analysis query

simenkg — Fri, 01 Aug 2014 10:57:09 GMT

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

Re: Set Analysis query

Fri, 01 Aug 2014 11:10:22 GMT

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

Re: Set Analysis query

simenkg — Fri, 01 Aug 2014 11:17:59 GMT

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)

Re: Set Analysis query

Fri, 01 Aug 2014 11:20:21 GMT

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

Re: Set Analysis query

rahulhv1 — Sat, 02 Aug 2014 04:52:42 GMT

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