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

];

[olivierrobin]

hello

when loading 3rd table, add a where clause

where isnull(%ShipAgrmID) or exists(%ShipAgrmID)

assuming you loaded the other tables first

[olivierrobin]

oups !!!!

it is not(exists())

sorry

[YoussefBelloum]

Hi,

here is one approach (I don't have all the info like the key between the tables and table you want to keep)

[Agreement]:

LOAD * INLINE [

    %ID, #Ag_Q

    ID1, 100

    ID2, 200 

    ID3, 250

];

left keep([Agreement])  //'Shipment' has only records where %ID exists in 'Agreement'.

[Shipment_1]:

LOAD *  INLINE [

    %ID, %ShipAgrmID, #Qty

    ID1, , 10

    ID2, A, 10

    ID2, A, 10

    ID3, C, 10

];

DROP Table Agreement;

[ShipAgreement]:

LOAD AutoNumber(%ID,%ShipAgrmID) as key, *  INLINE [

    %ID, %ShipAgrmID, #Sh_Q

    ID1, A, 50

    ID2, B, 40

];

Shipment:

LOAD AutoNumber(%ID,%ShipAgrmID) as key,*

Resident Shipment_1

Where len(trim(%ShipAgrmID))=0 or Exists(%ShipAgrmID);  //this line is where I put the last two conditions you have

drop Table Shipment_1;

DROP Field %ID from Shipment;

DROP Field %ShipAgrmID From Shipment;

[bramkn]

Rename %ShipAgrmID in table two and do load of table three with a where exists on that new field name. After you can change the field name back to %ShipAgrmID

[sergeyay]

Do you meen:

[Agreement]:

... //load

[ShipAgreement]:

... //load

[Shipment]:

LOAD * INLINE [

    %ID, %ShipAgrmID, #Qty

    ID1, , 10

    ID2, A, 11

    ID2, A, 12

    ID3, C, 10   

]

where isNull (%ShipAgrmID) or Exists(%ShipAgrmID); //wrong

?

I've got 2 rows in 'Shipment', but I need 3. Sorry, maybe my picture is not correct.

Not Exists() is wrong too.

[olivierrobin · Accepted solution]

try

where len(trim(%ShipAgrmID))=0 or Exists(%ShipAgrmID)

[sergeyay]

I've got it!

[Agreement]:

... //load

[ShipAgreement]:

... //load

[Shipment]:

LOAD * INLINE [

    %ID, %ShipAgrmID, #Qty

    ID1, , 10

    ID2, A, 11

    ID2, A, 12

    ID3, C, 10  

]

where %ShipAgrmID = '' or Exists(%ShipAgrmID); //not Null but empty string !

Now it's ok, thank you!

[sasiparupudi1]

Try using applymaps

LOAD * INLINE [

    %ID, #Ag_Q

    ID1, 100

    ID2, 200

    ID3, 250

];

MAP_Agreement:

Mapping Load

%ID, #Ag_Q

Resident

Agreement;

Drop table Agreement;

[ShipAgreement]:

LOAD * INLINE [

    %ID, %ShipAgrmID, #Sh_Q

    ID2, A, 50

    ID2, B, 40

];

MAP_ShipAgreement:

Mapping Load

%ID&'|'&%ShipAgrmID,#Sh_Q

Resident

ShipAgreement;

Drop Table ShipAgreement;

[Shipment]:

LOAD * INLINE [

    %ID, %ShipAgrmID, #Qty

    ID1, , 10

    ID2, A, 11

    ID2, A, 12

    ID3, C, 13

];

Final:

Noconcatenate Load

  %ID, %ShipAgrmID, #Qty

  Resident

Shipment

WHERE

Len(Trim(%ShipAgrmID))=0

Or

Isnull(ApplyMap('MAP_ShipAgreement',%ID&'|'&%ShipAgrmID,Null()))=0

;Drop Table Shipment;

[sergeyay]

What advantage to use applymaps? The code became much complicated. But I like the result!