Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shyamcharan
Creator III
Creator III

Count with AND & NOT Selections

Hi Experts,

I have a AND-mode selection requirement where I have to implement AND-mode in List Box as in here

Just a little description on this:

If I have patients Sam, John, Antony and Alex as show in the data below.

I need to show a patient who had multiple drugs at a time but not just the one selected.

For example: If I want to see a patient who had Drug2 AND Drug5, I should see John and Antony only

but not Sam or Peter(though they had Drug2 OR Drug5, my requirement is to show only those who had both the drugs)

  EPCRID, Patient, Drug

    12345, Sam, Drug1

    12345, Sam, Drug2

    12345, Sam, Drug3

    12345, Sam, Drug4

    54321, John, Drug2

    54321, John, Drug5

    98765, Antony, Drug2

    98765, Antony, Drug4

    98765, Antony, Drug5

    22222, Peter, Drug4

    22222, Peter, Drug5

    99999, Alex, Drug4

To acheive this I have implemented the AND-mode join in the load script. Please find the attached QVW.

However, now my requirement is

1) To show count of patients with selected drug. in this case the count should ignore the NOT(excluded brown color) selections

For example: AndMode Selection = &Drug2, !Drug5 Result = Count of patients who had Drug2

2) To show count of patients with selected drug and considering not having excluded(NOT) drug

For example: AndMode Selection = &Drug2, !Drug5 Result = Count of patients who had Drug2 but did not had Drug5


Can I achieve this? Thanks in advance.


Shyam

23 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

>>But, how can i get count based on the logic

Count(Distinct EPCRID)

I don't think there is any way to override the Not selection only. Set analysis overrides the selections on a field, but it does not differentiate between the individual parts of an AND selection. The counts could be like this'

Count({<EPCRID = P({<Drug = {'Drug2'}>} EPCRID)>} Distinct EPCRID)

or Count({<Drug = {'Drug2'}>} Distinct EPCRID)


Count({<EPCRID = P({<Drug = {'Drug2'}>} EPCRID) * E({<Drug = {'Drug5'}>} EPCRID)>} Distinct EPCRID)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
tresesco
MVP
MVP

If you use Count( Distinct EPCRID ) that should take care of the cases when a patient would have multiple visits. However, for first requirement: I guess it would be difficult (if not impossible) to ignore the 'NOT Selection'. Because I don't know a method to separate out 'AND Selection' and 'NOT Selection' here. You can probably use alternate state to handle this requirement. Refer vgutkovsky‌'s proposed solution for a clue in the post you referred.

sunny_talwar

May be this for the first req:

=Count(DISTINCT {<Drug = {$(=Replace(RTrim(Replace(Replace(Replace(SubField(GetFieldSelections(andDrug), '!', 1), ',', Chr(39) & ',' & Chr(39)), '&', Chr(39)), ',' & Chr(39), '    ')), '    ', ','))}, andDrug>} Patient)


Capture.PNG

Basically, I tried to trim down your selections and not selections

selections -> &

not selections -> !

From there onward it was mostly getting it into a format which can be used in set analysis.... may be there is a better way to do this string manipulation, but this was the best I was able to come up with..

Best,

Sunny

tresesco
MVP
MVP

Did not work much on NOT and AND mode selections. I never realized that getfieldselections() could return the field values with '&' and '!' indicators. That's a good notice and solution accordingly.

However, when I try few things with this, it looks very different in behavior (other than the selection logic).

GetSelectedCount(Field) for one &selection and one !selection gives 1 as output

Capture.JPG

while Count(Distinct Field) gives 4

Capture2.JPG

Could you explain this behavior ? Sorry, if this is already documented somewhere, I did not come across.

sunny_talwar

AFAIK Count() function usually counts all the possible (and available) selections. In this case Count(DISTINCT andDrug) is counting Drug1, Drug2, Drug3, and Drug4. Whereas GetSelectedCount(), counts this which are directly selected in the field. In this case, although we have two selections (selection and not selection), it appears to be counting the selection only, I believe (don't know why).

The best case to see the difference between Count(DISTINCT) and GetSelectedCount() is when no selection has been made. Count(DISTINCT) will count DISTINCT will show the distinct count of the field, whereas GetSelectedCount will show 0 because you have not selected anything in the field.

I am not sure if I answered what you asked for, but even I am not 100% sure about the behavior. May be hic‌ can share his insights.

Best,

Sunny

hic
Former Employee
Former Employee

Sunny's answer is pretty much it.

Count(distinct ...) counts all possible values - which is different from the selected ones. A field value can be selected without being possible. See Colors, States and State vectors.

Then we have the field functions that operate on the symbol tables and count the values in the different states :

GetSelectedCount()

GetAlternativeCount()

GetPossibleCount()

GetExcludedCount()

GetNotSelectedCount()

Of course, the GetSelectedCount only counts the the positive (green) selections. To count the red ones you should use GetNotSelectedCount.

HIC

shyamcharan
Creator III
Creator III
Author

Hi Gurus,

Thanks a ton for all your responses in helping me find the solution.

It a great help for me indeed.

Sunny, thanks heaps for the formula. I am going to try it out and will post the output.

Appreciate a lot.

Sincerely,

Shyam

shyamcharan
Creator III
Creator III
Author

Hi Sunny,

Thanks again for your time and help here.

I have tried your formula and it is working well when there are AND & NOT selections in the andDrug field.

But, it does not work when NOT selection is not made.

Is it how it will work?

Thanks in advance.

shyamcharan
Creator III
Creator III
Author

Hi Sunny,

Sorry, it looks like its not working when I select two AND values (along with a NOT) as shown in the attachment.

In the attached QVW, I have selected Drug2 AND Drug4 along with a NOT Drug3.

The result ignoring the NOT selection should be = 2 as there are two patients sam and Antony who had both the drugs Drug2 AND Drug4.


But the output is 5.


Any idea how can this be fixed?


Thanks again.

tresesco
MVP
MVP

Thanks Sunny, HIC,

Now I notice that even if one is selected (green) few are there in white (possible). This is not the case very often we see in general selection and so I got confused. And yes - the GetNotSelectedCount() answers and clears the actual doubt. Thanks you both once again. Few things are clearer now.