Exclude records based on 2 related tables

[#1]

I have a Group table, containing:

Dept, Flag

1,A

2,B

And Master Table containing:

SKU, Dept

S1,1

S2,1

S3,1

S4,2

S5,3

S6,3

And a Sales Table containing:

TRX, SKU

1, S1

1, S2

2, S1

2, S5

3, S3

3, S4

3, S5

4, S4

5, S6

5, S1

What I need to have a new Sales table having only transactions having their SKUs exclusively belonging to the Group table, (i.e transaction of items in Dept 1 & 2 exclusively).   Transactions having items from dept 3 & any other dept  are to be excluded. i.e:  TRX number 1 only matches the criteria.

How to create this table using the script?

[Anonymous]

Tony,

Sorry - I don't understand the "desired result" attachement.  Per the requirements, the only excluded Master records sholud be with SKU S5 and S6 - in this case the excluded records in Sales are
S5   2
S5   3
S6   5

What did I get wrong?

[#3]

Michael,

What you did is not wrong in case we want to exclude SKUs based on their Dept.

What is required here, is to exclude TRX, including all its SKUs, having at least 1 SKU in the Dept to exclude.

so TRX 2 containing S1 & S5 is excluded

TRX 3 containing S3, S4 & S5 is excluded

TRX 5 containing S1 & S6 is excluded

What remains is TRX1 (S1 & S2) & TRX 4 (S4)

[Anonymous]

I see what you mean...

Attached is an example.  I loaded all the data as is, implemented in the exclusion in the front-end expression:

only({<TRX=E({<SKU=E({<SKU-=E({<Flag={'*'}>})>})>})>} TRX)

Regards,

Michael

[#5]

Thank you Michael,

Complex expression that gave the exact result.

I would had preferred to seperate them in the script, so that It can be used in separate sheets

Regards,

Tony

[Anonymous]

Tony,

This is possible as well, although I don't see any advantage...  Anyway, see attached.  I didn't remove the previous script, just added some more.  You can keep all, but in this case you need to qualify or to rename fields.

Regards,

Michael