Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
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

1 Solution

Accepted Solutions
sunny_talwar

Try this

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

View solution in original post

23 Replies
shyamcharan
Creator III
Creator III
Author

Experts, Can I please get some help here?

shraddha_g
Partner
Partner

stalwar1‌ one who can help you better with expressions

tresesco
MVP
MVP

I guess it's working fine.

Capture.JPG

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


shyamcharan
Creator III
Creator III
Author

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


tresesco
MVP
MVP

What is the expected output against the current selection?

shyamcharan
Creator III
Creator III
Author

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)

tresesco
MVP
MVP

Your second requirement :

Capture.JPG

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

shyamcharan
Creator III
Creator III
Author

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

shyamcharan
Creator III
Creator III
Author

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.