Smart load. How to keep & remove some records?

[sergeyay]

Hi all!

I have some problem, but have not experience to resolve it 

There are 3 tables. 1st and 2nd is ok, but 3rd need transformations. Here is simplefied scheme:

'Shipment' has only records where %ID exists in 'Agreement'.

The rule of 'Shipment's transformation:

• to keep records where %ShipAgrmID is empty (there is no shipment agreement);
• to remove records where %ShipAgrmID not exists in 'ShipAgreement'.

Could you give me any idea? I think the solution is simple and beautiful ))

Regards

Sergei

[Agreement]:

LOAD * INLINE [

    %ID, #Ag_Q

    ID1, 100

    ID2, 200

    ID3, 250

];

[ShipAgreement]:

LOAD * INLINE [

    %ID, %ShipAgrmID, #Sh_Q

    ID2, A, 50

    ID2, B, 40

];

[Shipment]:

LOAD * INLINE [

    %ID, %ShipAgrmID, #Qty

    ID1, , 10

    ID2, A, 11

    ID2, A, 12

    ID3, C, 13

];

[YoussefBelloum]

Applymap() is much faster than a join for example when you load heavy data