topic Re: Count with AND & NOT Selections in QlikView

Count with AND & NOT Selections

shyamcharan — Wed, 08 Nov 2017 02:21:17 GMT

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

Re: Count with AND & NOT Selections

shyamcharan — Thu, 09 Nov 2017 05:28:03 GMT

Experts, Can I please get some help here?

Re: Count with AND & NOT Selections

shraddha_g — Thu, 09 Nov 2017 05:42:11 GMT

stalwar1‌ one who can help you better with expressions

Re: Count with AND & NOT Selections

tresB — Thu, 09 Nov 2017 05:43:47 GMT

I guess it's working fine.

Patient (ID) who bought 'Drug2' are - 12345, 54321 and 98765

Patient (ID) who bought 'Drug5' are - , 54321 and 98765 and 22222

Hence who bought 'Drug2' and NOT 'Drug5' is 12345

Re: Count with AND & NOT Selections

shyamcharan — Thu, 09 Nov 2017 05:46:07 GMT

Hi Tresesco,

Thanks for reply.

My requirement is something like below:

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

Re: Count with AND & NOT Selections

tresB — Thu, 09 Nov 2017 05:51:31 GMT

What is the expected output against the current selection?

Re: Count with AND & NOT Selections

shyamcharan — Thu, 09 Nov 2017 05:55:51 GMT

Current Selections = &Drug2, !Drug4

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

Result for this based on current selection is 3 (Sam, John, Antony)

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

Result for this based on current selection is 1 (John)

Re: Count with AND & NOT Selections

tresB — Thu, 09 Nov 2017 06:07:59 GMT

Your second requirement :

For first : You mean you want to ignore the 'NOT Selection' ?

Re: Count with AND & NOT Selections

shyamcharan — Thu, 09 Nov 2017 06:13:21 GMT

For first : You mean you want to ignore the 'NOT Selection' ? Yes

Re: Count with AND & NOT Selections

shyamcharan — Thu, 09 Nov 2017 06:21:23 GMT

Tresesco,

Sorry. I don't meant to be rude here .

But, how can i get count based on the logic you provided for the second requirement?

Also, a patient could have more than one EPCRID when he visits hospital again.

I need to count the EPCRID rather the patient.

Thanks in advance.

Re: Count with AND & NOT Selections

jonathandienst — Thu, 09 Nov 2017 07:07:01 GMT

>>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)

Re: Count with AND & NOT Selections

tresB — Thu, 09 Nov 2017 07:08:43 GMT

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.

Re: Count with AND & NOT Selections

sunny_talwar — Thu, 09 Nov 2017 17:46:04 GMT

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)

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

Re: Count with AND & NOT Selections

tresB — Fri, 10 Nov 2017 05:12:15 GMT

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

while Count(Distinct Field) gives 4

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

Re: Count with AND & NOT Selections

sunny_talwar — Fri, 10 Nov 2017 12:29:29 GMT

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

Re: Count with AND & NOT Selections

hic — Fri, 10 Nov 2017 13:08:50 GMT

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

Re: Count with AND & NOT Selections

shyamcharan — Sat, 11 Nov 2017 13:24:08 GMT

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

Re: Count with AND & NOT Selections

shyamcharan — Mon, 13 Nov 2017 02:58:10 GMT

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.

Re: Count with AND & NOT Selections

shyamcharan — Mon, 13 Nov 2017 03:14:40 GMT

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.

Re: Count with AND & NOT Selections

tresB — Mon, 13 Nov 2017 04:57:23 GMT

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.