Count Where One Value is Not In Another Field

[JustinDallas]

Hello Folks,

I have a datamodel that has three tables.  On the UI side, I have a straight table showing the data.  What I would like to do is count the number of times a value in Field A does not appear in Field B.

For example, the data set below:

AllowedTerminals:
LOAD * Inline
[
    'Bill To Terminal', 'Order Bill To Id'
    'GSP', 'IBM',
    'CLT', 'IBM',
]
;

Orders:
LOAD * Inline
[
    'Order #', 'Order Bill To Id'
    '18CA7B', 'IBM'
    '63D95E', 'IBM',
]
;


Assets:
LOAD * Inline
[
    'Order #', 'Tractor', 'Tractor Terminal'
    '18CA7B', '123 Freightliner', 'GSP',
    '63D95E', '900 Mack', 'LAX'
]
;

EXIT Script
;

In the example above, the tractor used for Order #18CA7B is terminaled at GSP.  GSP exists in the [Bill To Terminal] field that is linked to the [Order Bill To Id] of IBM.

However, Order #63D95E used a tractor terminaled at LAX, and LAX is not in the [Bill To Terminal] list.

I'm trying to get something like the following where the field CrossTerminalCount reflects the number of times this has happened.

Order #	Order Bill To Id	Tractor Terminal	CrossTerminalCount
18CA7B	IBM	GSP	0
63D95E	IBM	LAX	1

Order Bill To Id	CrossTerminalCount
IBM	1

The only solution that I can think of revolves around using the P() function, but every time my mind has gone in that direction, I've always had to backtrack.

Any help is greatly appreciated.

[GaryGiles · Accepted solution]

You're right, the P() is tempting, but try this:

sum(if(aggr(sum(if([Tractor Terminal]=[Bill To Terminal],1,0)), [Order #]) = 0 ,1,0)

Not sure completely of all of the rules you want to apply.  Does it count if there is a [Bill To Terminal] that exists but isn't associated with the [Order #] via the [Order Bill to Id] - [Order #] association?