Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
apoorvasd
Creator II
Creator II

'AND' and 'OR' operators in Set Analysis

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":

IDTitleStatus 1Status 2
1Test 1N/AN/A
2Test 1WILL NOT DOAPPROVED
3Test 2AAAPPROVED

(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.

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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

   )

View solution in original post

16 Replies
apoorvasd
Creator II
Creator II
Author

Anybody got a chance to look into this?

johncaqc
Specialist
Specialist

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

apoorvasd
Creator II
Creator II
Author

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.

tresesco
MVP
MVP

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

   )

View solution in original post

timpoismans
Specialist
Specialist

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)

ANDOR.PNG

The first table is just the data set,

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

timpoismans
Specialist
Specialist

Hi Tresesco

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

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

Thanks!

apoorvasd
Creator II
Creator II
Author

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.

apoorvasd
Creator II
Creator II
Author

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.

tresesco
MVP
MVP

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.