Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
Valued Contributor

Re: Inverse Selection or Where Not Exists??? Please Help

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

6 Replies

Re: Inverse Selection or Where Not Exists??? Please Help

Maybe something like this:

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

- Marcus

Employee
Employee

Re: Inverse Selection or Where Not Exists??? Please Help

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
Valued Contributor

Re: Inverse Selection or Where Not Exists??? Please Help

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
Valued Contributor

Re: Inverse Selection or Where Not Exists??? Please Help

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

Re: Inverse Selection or Where Not Exists??? Please Help

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

Employee
Employee

Re: Inverse Selection or Where Not Exists??? Please Help

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.

Community Browser