If condition within set expression on field selected

[Anonymous]

Hi Qlik experts,

I had below set expression which provides count as '2'.

It is a set expression ignoring current selections though there is a selection on field %_SITEID.

=count({1<

CALCULATED_SITE_STATUS={"Active"},

%_SITEID= {"194","201"}

>} DISTINCT %_SITEID)

Same expression with an if clause gives result as '0'.

=count({1<

CALCULATED_SITE_STATUS={"Active"},

%_SITEID= {"=if(match(%_SITEID,194,201),1,0)=1"}

>} DISTINCT %_SITEID)

Any advise how to use an if clause inside set expression and by ignoring current selections though there exists a selection on field used inside set expression if clause.

Thanks in advance.

[arvind1494]

store if condition in variable

and then use that in set expression

[jyothish8807]

try this,

count({1<

CALCULATED_SITE_STATUS={"Active"},

%_SITEID= {"=if(match(%_SITEID,'194','201'),%_SITEID)"}

>} DISTINCT %_SITEID)

Br,

KC

[Chanty4u]

try this

if(match(%_SITEID,'194','201'),

count({1<

CALCULATED_SITE_STATUS={"Active"},

>} DISTINCT %_SITEID)

If condition in set analysis

[qlikviewwizard]

Hi,

Can you upload some sample data?

[sunny_talwar · Accepted solution]

Not sure I understand, but may be this

=Count({1<CALCULATED_SITE_STATUS = {"Active"}, %_SITEID = {"=If(Match(Only({1} %_SITEID), 194, 201), 1, 0) = 1"}

>} DISTINCT %_SITEID)

[Anonymous]

Thanks all.

Sunny, yes Only{1} inside match did the magic. Thanks.

I have also tried other advises below where the result is not as expected.

Gives -0

If clause in a variable and use variable in set instead.

Gives - 0

count({1<

CALCULATED_SITE_STATUS={"Active"},

%_SITEID= {"=if(match(%_SITEID,'194','201'),%_SITEID)"}

>} DISTINCT %_SITEID)

Gives - '-'

if(match(%_SITEID,'194','201'),

count({1<

CALCULATED_SITE_STATUS={"Active"},

>} DISTINCT %_SITEID)

To provide a view of sample data for others visiting this post -

MYTABLE:

LOAD

*

INLINE

[

%_SITEID,CALCULATED_SITE_STATUS

194,Active

201,Active

205,Active

206,Active

212,Inactive

214,Inactive

];

Step 1 : A list box on sheet for %_SITEID

Step2 : Select %_SITEID as 205

Step3: Requirement is to give me count of only records where %_SITEID is 194 or 201 and with CALCULATED_SITE_STATUS as Active though selection of %_SITEID is for 205.

It is underlying scenario i was looking for though it is a subset of bigger set query.

Thanks all.