topic Re: If condition within set expression on field selected in QlikView

If condition within set expression on field selected

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: If condition within set expression on field selected

arvind1494 — Sun, 10 Jun 2018 09:23:19 GMT

store if condition in variable

and then use that in set expression

Re: If condition within set expression on field selected

jyothish8807 — Sun, 10 Jun 2018 09:44:40 GMT

try this,

count({1<

CALCULATED_SITE_STATUS={"Active"},

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

>} DISTINCT %_SITEID)

Br,

Re: If condition within set expression on field selected

Chanty4u — Sun, 10 Jun 2018 11:54:32 GMT

try this

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

count({1<

CALCULATED_SITE_STATUS={"Active"},

>} DISTINCT %_SITEID)

If condition in set analysis

Re: If condition within set expression on field selected

qlikviewwizard — Mon, 11 Jun 2018 02:07:01 GMT

Hi,

Can you upload some sample data?

Re: If condition within set expression on field selected

sunny_talwar — Mon, 11 Jun 2018 02:16:38 GMT

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)

Re: If condition within set expression on field selected

Anonymous — Mon, 11 Jun 2018 08:47:34 GMT

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.