topic Re: Inverse Selection or Where Not Exists??? Please Help in QlikView

Inverse Selection or Where Not Exists??? Please Help

Wed, 02 Dec 2015 00:43:16 GMT

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

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

marcus_sommer — Wed, 02 Dec 2015 01:50:42 GMT

Maybe something like this:

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

- Marcus

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

JonnyPoole — Wed, 02 Dec 2015 02:09:15 GMT

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.

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

eduardo_sommer — Wed, 02 Dec 2015 02:10:18 GMT

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

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

eduardo_sommer — Wed, 02 Dec 2015 02:13:49 GMT

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

marcus_sommer — Wed, 02 Dec 2015 02:19:19 GMT

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

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

JonnyPoole — Wed, 02 Dec 2015 02:29:47 GMT

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.