topic Re: Need help with Set Analysis syntax in QlikView

Need help with Set Analysis syntax

Tue, 14 Jul 2015 14:17:05 GMT

1) I am trying to convert the below IF statement using set analysis. The below IF statement is capturing correctly the rows I need. It even displays rows when RGroup is null....

if(([01-Opportunity WL] ='x' or [02-Engagement WL]='x' or [06-OPTT]='x') and RGroup <> '10',[User role])

2) I tried to convert the above IF statement using Set Analysis but it does not count if the RGroup field is NULL...

count({$<[01-Opportunity WL]={"x"},[02-Engagement WL]={"x"},[06-OPTT]={'x'},RGroup-={"10"}>} distinct [User Name])

3) Then I tried using the below set analysis to capture RGroup is NULL and if RGroup is NOT equal to '10' but it doesn seem to be working...

Count({-<RGroup-={'*'}> +<RGroup-={'10'}> + <[01-Opportunity WL]={'x'}>*<[02-Engagement WL]={'x'}>*<[06-OPTT]={'x'}>} distinct [User Name])

Is my syntax wrong? Am I useing the '+' and '*' inccorrectly?

Re: Need help with Set Analysis syntax

MK_QSL — Tue, 14 Jul 2015 14:21:27 GMT

=count({$<[01-Opportunity WL]={"x"},[02-Engagement WL]={"x"},[06-OPTT]={'x'},RGroup-={'10'}>} distinct [User role])

Re: Need help with Set Analysis syntax

Tue, 14 Jul 2015 14:26:25 GMT

Hi Manish,

Thanks for your quick response! I had your statement originally but...

I also need where RGroup is null or blank. When I use the above statement it doesn not count the null values where RGroup is null or blank just the row where there are values for RGroup.

Any suggestions?

Re: Need help with Set Analysis syntax

MK_QSL — Tue, 14 Jul 2015 14:29:54 GMT

Create a Flag in your Script just below RGroup

IF(RGroup <> 10 and (IsNull(RGroup) or Len(Trim(RGroup))=0, 1, 0) as Flag

Now you can use

=count({$<[01-Opportunity WL]={"x"},[02-Engagement WL]={"x"},[06-OPTT]={'x'},Flag={1}>} distinct [User role])

Re: Need help with Set Analysis syntax

Tue, 14 Jul 2015 14:33:30 GMT

Thanks! Is there any other way to do this using set analysis and not creating a flag in the script?

Re: Need help with Set Analysis syntax

settu_periasamy — Tue, 14 Jul 2015 14:37:33 GMT

May be like this

=Count({<RGroup-={'*'},RGroup-={'10'}> +

<[01-Opportunity WL]={'x'},RGroup-={'10'}>+

<[02-Engagement WL]={'x'},RGroup-={'10'}>+

<[06-OPTT]={'x'},RGroup-={'10'}>} distinct [User Name])

Re: Need help with Set Analysis syntax

Tue, 14 Jul 2015 15:14:11 GMT

Thank you Settu but that did not work.