Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Inverse Selection or Where Not Exists??? Please Help

Good Morning Everyone

Simple as it sounds, I am very confused over this and don't really know what I am doing. Please consider the following table:

ItemCode          EventCode

001                    Start

001                    Finish

002                    Start

002                    Finish

003                    Finish

004                    Start

I want to know the number of Item codes that have a finished Event code which have also got a start event code as well, in this case the number would be 2, but I need the formula...help!!

Kind Regards,

Miles

1 Solution

Accepted Solutions
eduardo_sommer
Partner - Specialist
Partner - Specialist

If your data can contain more than one Start or more than one Finish EventCode for an ItemCode, you can use the following expression:

=count({<ItemCode=p({<EventCode={'Start'}>} ItemCode)>*<ItemCode=p({<EventCode={'Finish'}>} ItemCode)>}DISTINCT ItemCode)

this will count all the ItemCode that have, at least, one Start EventCode and, at least, one Finish EventCode

Regards

Eduardo

View solution in original post

6 Replies
marcus_sommer

Maybe something like this:

sum(aggr(if(count(ItemCode)=2, 1), ItemCode))

- Marcus

JonnyPoole
Former Employee
Former Employee

I was wondering if this maybe slightly more resilient (it ensures that an individual item code has both , and only, Start and FInish as an Event Code.

sum(  if(aggr(concat(DISTINCT EventCode,'-',EventCode),ItemCode)='Finish-Start',1))

A script / data model solution would also make either one more scalable at higher data volume.

eduardo_sommer
Partner - Specialist
Partner - Specialist

If your data can contain more than one Start or more than one Finish EventCode for an ItemCode, you can use the following expression:

=count({<ItemCode=p({<EventCode={'Start'}>} ItemCode)>*<ItemCode=p({<EventCode={'Finish'}>} ItemCode)>}DISTINCT ItemCode)

this will count all the ItemCode that have, at least, one Start EventCode and, at least, one Finish EventCode

Regards

Eduardo

eduardo_sommer
Partner - Specialist
Partner - Specialist

Hi, Jonathan

I think you should consider both 'Finish-Start' and 'Start-Finish', since the events can be in any order in your table.

Eduardo

marcus_sommer

Yes a pre-calculation on script-level makes the gui-expressions easier and also the performance would be better because the aggr-functions are quite RAM expensive.

- Marcus

JonnyPoole
Former Employee
Former Employee

Its good to be thinking of all possibilities.

In this sitation, the 3rd parameter of the concat() function defines the sort-weight. It should cause an alphabetical order based on using 'ItemCode' as the argument.