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?

[Jason_Michaelides]

Surely all sales rows with SKU <= S4 should be included?

[#3]

Dear Jason,

The condition is that the transaction should contain all its items belonging to department having a valid Flag in the Group table.

Thanks for your reply

[Anonymous]

Hi you could try somthing like:

[Master1]:

LOAD * INLINE [

          SKU,Dept

          S1,1

          S2,1

          S3,1

          S4,2

          S5,3

          S6,3

];

Join ([Master1])

LOAD * INLINE [

          Dept, Flag

          1,A

          2,B

          ];

Join ([Master1])

LOAD * INLINE [

          TRX, SKU

          1, S1

          1, S2

          2, S1

          2, S5

          3, S3

          3, S4

          3, S5

          4, S4

          5, S6

          5, S1

];

Master:

Load '' as Q,

          *

          Resident Master1

          Where Dept  < '3';

Drop table Master1 ;

Drop Field Q from Master;

[Anonymous]

Or :

[Master1]:

LOAD * INLINE [

          SKU,Dept

          S1,1

          S2,1

          S3,1

          S4,2

          S5,3

          S6,3

];

Join ([Master1])

LOAD * INLINE [

          Dept, Flag

          1,A

          2,B

          ];

Join ([Master1])

LOAD * INLINE [

          TRX, SKU

          1, S1

          1, S2

          2, S1

          2, S5

          3, S3

          3, S4

          3, S5

          4, S4

          5, S6

          5, S1

];

Master:

Load '' as Q,

          *

          Resident Master1

          Where len(Flag)  > '0';

Drop table Master1 ;

Drop Field Q from Master

[Anonymous]

The easiest way is to use left keep load:

Group:

LOAD

...;

//

Master:

LEFT KEEP (Group) LOAD

...;

//

Sales:

LEFT KEEP (Master) LOAD

...;

[Anonymous]

This last one only will load the data where the lenght of the field flag is smaller then '0'  (so not exsistin)

[Jason_Michaelides]

That's what I thought.  But your Master table has SKU values S1,S2,S3 and S4 all with Dept 1 or 2 which have valid flags in the Group table. So shouldn't all TRX values be include as they all have at least one record with a SKU value associated to a valid Dept?  Or do you only want Sales rows where all TRX values are related to valid Depts?

Dennis, what if there are more than 3 departments...?

EDIT: I have re-read your response and you have answered my question already!  Sorry...

[#9]

Thanks Michael,

you are close.  Attach is the desired result.  TRX having items not in master, should be eliminated entirely.

Thus the result will be only for TRX no.1 & 4

We need an additional small step to reach this result.

[#10]

Thanks Michael,

you are close.  Attach is the desired result.  TRX having items not in master, should be eliminated entirely.

Thus the result will be only for TRX no.1 & 4

We need an additional small step to reach this result.