topic Re: 'AND' and 'OR' operators in Set Analysis in QlikView

'AND' and 'OR' operators in Set Analysis

apoorvasd — Tue, 25 Sep 2018 13:10:15 GMT

Hello Everyone,

Need help in set analysis expression.

I have a table which looks like below. I need to get the count of "Title" where the following conditions satisfy for "Title":

ID	Title	Status 1	Status 2
1	Test 1	N/A	N/A
2	Test 1	WILL NOT DO	APPROVED
3	Test 2	AA	APPROVED

(Status 1 is "N/A") or (Status 1 is "WILL NOT DO" and Status 2 is "APPROVED").

That means, if the "Title" has Status 1 as "N/A" and there are no other rows with the same "Title", then this should be counted as 1. And therefore my result is 1.

If there are more than one row with same "Title", then two things apply here:

1. the query has to check if Status 1 is "N/A" for all rows. If Yes, then count this as 1. If No, then do the below

2. If few rows have Status 1 as "N/A" and few have Status 1 as "WILL NOT DO", then the query should check if Status 2 is "APPROVED" for rows which have Status 1 as "WILL NOT DO". If all these conditions satisfy then count this as 1 for that particular "Title", else don't count.

In my example above, the expression should return 1 as the result .

I tried the following expression, but this isn't giving me the right results. Or can I achieve this using "If" statement?

Count({$<[Status 1]={"N/A"}> , [Status 1]={"WILL NOT DO"}> * <[Status 2]={"APPROVED"}>} DISTINCT Title)

Any help on this?

Thank you.

Re: 'AND' and 'OR' operators in Set Analysis

apoorvasd — Tue, 25 Sep 2018 16:02:21 GMT

Anybody got a chance to look into this?

Re: 'AND' and 'OR' operators in Set Analysis

johnca — Tue, 25 Sep 2018 16:19:43 GMT

Use "+" as OR...

Change this

Count({$<[Status 1]={"N/A"}> , [Status 1]={"WILL NOT DO"}> * <[Status 2]={"APPROVED"}>} DISTINCT Title)

to this

Count({$<[Status 1]={"N/A"}> + <[Status 1]={"WILL NOT DO"}> * <[Status 2]={"APPROVED"}>} DISTINCT Title)

HTH,

John

Re: 'AND' and 'OR' operators in Set Analysis

apoorvasd — Wed, 26 Sep 2018 05:44:51 GMT

Hi John,

Thanks for your response.

With the example I have provided above, the expression works.

Here is the new set of data

LOAD * Inline

[

Id, Title, Status1, Status2

1, Test1, N/A, N/A

2, Test1, WILL NOT DO, APPROVED

3, Test2, N/A, N/A

4, Test2, WILL NOT DO, APPROVED

5, Test3, WILL NOT DO, APPROVED

6, Test3, AA, N/A

];

With this data I expect the result to be 2 (Title, "Test3" hasn't fulfilled the condition). But, using your expression gives me 3 as the output. Can you help me with this new set of data?

Thank you.

Re: 'AND' and 'OR' operators in Set Analysis

tresB — Wed, 26 Sep 2018 06:38:35 GMT

I guess, you could try something like:

=Count(DISTINCT {<Title={"=count( {<Status1={'N/A'}>}Id)=count(Id)"}>

( <Title={"=count( {<Status1={'N/A'}>}Id)>0"}>

<Title={"=count( {<Status1={'WILL NOT DO'}>}Id)=count({<Status2={'APPROVED'}>}Id)"}>

)

}Title

)

Re: 'AND' and 'OR' operators in Set Analysis

timpoismans — Wed, 26 Sep 2018 06:54:33 GMT

Hi Apoorva

With the new data set, I've come up with the following If-statement:

If(WildMatch(Aggr(Concat(Status1),Title),'*N/A*'),

If(WildMatch(Aggr(Concat(Status1),Title),'*WILL NOT DO*'),

If(WildMatch(Aggr(Concat(Status2),Title),'*APPROVED*'),Count(distinct Title)),

Count(distinct Title)))

The only downpart of it, is that it needs the dimension Title to function. (The Aggr function gives a concat string per title)

The first table is just the data set,

The second displays the result, but as mentioned, needs the Title dimension.

Re: 'AND' and 'OR' operators in Set Analysis

timpoismans — Wed, 26 Sep 2018 07:12:03 GMT

Hi Tresesco

Could you give me a quick rundown on how the following works exactly:

Title={"=count( {<Status1={'N/A'}>}Id)=count(Id)"}

Thanks!

Re: 'AND' and 'OR' operators in Set Analysis

apoorvasd — Wed, 26 Sep 2018 08:10:24 GMT

Hi Tim,

Thanks for your response!

I am creating a KPI (just a text box) to display this result. Hence would require the expression to work without adding any dimension. But this was helpful.

Thank you.

Re: 'AND' and 'OR' operators in Set Analysis

apoorvasd — Wed, 26 Sep 2018 08:14:37 GMT

Hi Tresesco,

This works as expected. Thanks a lot for your help!

I had never tried this kind of expression in set analysis. Would be great if you could explain that piece of code as mentioned by Tim!

Thank you.

Re: 'AND' and 'OR' operators in Set Analysis

tresB — Wed, 26 Sep 2018 08:30:58 GMT

Title={"=count( {<Status1={'N/A'}>}Id)=count(Id)"}

Searches for those Titles for which count(Id) for Status1='N/A' : count( {<Status1={'N/A'}>}Id),is equal to count(Id), i.e. - in other word there is no other Status1 member (except for 'N/A') for that title, else the counts would not match. And that was the first check need to be done by the OP.

Re: 'AND' and 'OR' operators in Set Analysis

timpoismans — Wed, 26 Sep 2018 08:40:38 GMT

Thanks for the info!

Re: 'AND' and 'OR' operators in Set Analysis

timpoismans — Wed, 26 Sep 2018 08:52:40 GMT

Thanks to Tresesco's explanation, I stumbled upon a possible issue.

Consider the following scenario tresesco‌ apoorvasd‌

Id, Title, Status1, Status2

1, Test1, N/A, N/A

2, Test1, WILL NOT DO, APPROVED

3, Test2, N/A, N/A

4, Test2, WILL NOT DO, N/A

5, Test2, N/A, APPROVED

6, Test3, WILL NOT DO, APPROVED

7, Test3, AA, N/A

Your result should be 1 in this case.

Test1 is OK.

Test2 is not OK. (Has N/A and WILL NOT DO, but no APPROVED on the same row)

Test3 is not OK. (Has no N/A as Status1)

Through Tresesco's measure, your result is 2 as he checks for the count of WILL NOT DO and APPROVED.

His measure will let Test2 through while it shouldn't.

I don't know if this is a possible scenario, but just thought I'd raise the issue.

Regards,

Tim P.

Re: 'AND' and 'OR' operators in Set Analysis

tresB — Wed, 26 Sep 2018 09:06:42 GMT

Thanks Tim. You are right. This is why we need to test with more such possible scenarios. The check has to be there at the Id level instead. A small correction should work I guess, like:

=Count(DISTINCT {<Title={"=count( {<Status1={'N/A'}>}Id)=count(Id)"}>

( <Title={"=count( {<Status1={'N/A'}>}Id)>0"}>

<Id={"=count( {<Status1={'WILL NOT DO'}>}Id)=count({<Status2={'APPROVED'}>}Id)"}>

)

}Title

)

However, I might have to wait till you come up with another glitch for final smiley.

Re: 'AND' and 'OR' operators in Set Analysis

timpoismans — Wed, 26 Sep 2018 09:16:02 GMT

Hehe, just trying to help.

I think it should work with the correction.

The smiley is final

Re: 'AND' and 'OR' operators in Set Analysis

apoorvasd — Fri, 28 Sep 2018 08:37:55 GMT

Thanks for the explanation!

Re: 'AND' and 'OR' operators in Set Analysis

apoorvasd — Fri, 28 Sep 2018 08:41:21 GMT

Hi Tim,

Thanks for coming up with this possibility. This was also the case in my data set. Hence altered my expression as suggested by Tresesco.

Thank you both!

Re: 'AND' and 'OR' operators in Set Analysis

apoorvasd — Fri, 28 Sep 2018 08:42:48 GMT

Thank you.