Inverse Selection or Where Not Exists??? Please Help

[#1]

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

[marcus_sommer]

Maybe something like this:

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

- Marcus

[JonnyPoole]

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 · Accepted solution]

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]

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]

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.