topic Re: Set Analysis in QlikView

Set Analysis

mishraamit2485 — Sat, 20 Sep 2014 05:36:14 GMT

Hi All,

I need one help here in writing one set analysis expression, I have the same condition in (if function) but i want to rewrite it using set.

here is the function

COUNT(DISTINCT if(isnull(Mother_Board)=FALSE(),TotalNoOfLaptops)).

2nd Question, is it possible for any definition if i write numerator using Set and denominator using if function, will this give me correct result or I have to use set analysis for both nume and deno or if function for both numerator and denominator.

Re: Set Analysis

sunilkumarqv — Sat, 20 Sep 2014 07:02:41 GMT

try like this

=If(Count(DISTINCT {<isnull(Mother_Board)={'FALSE()'}>}1),TotalNoOfLaptops)

Re: Set Analysis

mishraamit2485 — Sat, 20 Sep 2014 08:10:53 GMT

Thanks for your reply but its not working, and why are we using all the condition inside the if...

Re: Set Analysis

MK_QSL — Sat, 20 Sep 2014 08:47:23 GMT

COUNT(DISTINCT if(isnull(Mother_Board)=FALSE(),TotalNoOfLaptops))

COUNT({$<Mother_Board = {'*'}>}DISTINCT TotalNoOfLaotops)

Re: Set Analysis

PrashantSangle — Sat, 20 Sep 2014 08:49:35 GMT

Hi,

You need some field comapre in Set analysis

Like If you have Line wise data,

Then you can try like this,

Count({<Line_Id={"=not isNull(Mother_Board)"}>}TotalNoOfLaptops)

In you if statement

Try this,

COUNT(DISTINCT if(not isnull(Mother_Board),TotalNoOfLaptops)).

Regards

Re: Set Analysis

mishraamit2485 — Sat, 20 Sep 2014 11:01:18 GMT

HI Manish, looks like it is working fine not able to test it as do not have the data set.

Could please explain me how we are using {'*'}

Re: Set Analysis

MK_QSL — Sat, 20 Sep 2014 12:05:56 GMT

{'*'} will use only non null value Mother_Board

Re: Set Analysis

mishraamit2485 — Sat, 20 Sep 2014 14:17:01 GMT

Thanks manishkachhia tested it it's working fine , just by curiosity i'm asking what if I have to see only the null value instead of non null.

Is there any link you would suggest where I can refer for such kind of queries.

Re: Set Analysis

MK_QSL — Sun, 21 Sep 2014 07:34:27 GMT

Create a Flag in your script just below Mother_Board Field like below...

If(IsNull(Mother_Board) or Len(Trim(Replace(Mother_Board,'-','')))=0,1,0) as Monther_Board_Null_Flag

Now you can use as below

For Non Null

COUNT({$<Mother_Board = {'*'}>}DISTINCT TotalNoOfLaotops)

COUNT({$<Mother_Board_Flag = {0}>}DISTINCT TotalNoOfLaotops)

Try {0} or {'0'} or {"0"} all....

For Null

COUNT({$<Mother_Board_Flag = {'1'}>}DISTINCT TotalNoOfLaotops)