Exclude records from a previous load

[#1]

I have two files that I am loading; one is assets and the other book_parts.

The asset file will consist of CompanyId, AssetID, AcquisitionDate, etc/

The book_parts file will consist of CompanyId, AssetID, Sequence, BookID.

The book_parts file could have the assetid listed multiple times with varying information.

The assets file will look something like this:

Companyid     AssetID     AcquisitionDate

2                    5456          10/17/13

2                    10246          11/24/14

the book_parts file will look like this:

Companyid     Assetid     Sequence     BookID

2                    5456          0                         1

2                     5456          1                         1

2                    5456           2                         1

2                    10246         0                          1

I need to join the assets file & book_parts file but I need to somehow check the book_parts file and not pull the row that has a sequence of 0 if there is more then one sequence number for the same assetid.  If the assetid has only a 0 sequence then I need to keep that row.

So my end result should have:

2          5456     1     1

2          5456     2     1    

2          10246   0     1

Any suggestions on how to set this load up????

I know I need to load the records in first and I guess I somehow need to go back through the records to determine which ones to discard and then tie it to the assets file.

[maxgro]

RESULT

SCRIPT

a:

load

Companyid & '-' & Assetid as NewField, * inline [

Companyid ,    Assetid ,    Sequence  ,  BookID

2 ,                  5456    ,      0  ,                      1

2  ,                  5456    ,      1  ,                      1

2  ,                5456      ,    2    ,                    1

2    ,                10246      ,  0      ,                    1

];

b:

NoConcatenate

load *

Resident a

where not (Sequence=0 and NewField=Peek(NewField))

order by Companyid ,    Assetid ,    Sequence desc ;

drop table a;

[#3]

How does the peek work????

[maxgro]

I used peek to compare NewField with the previous NewField (NewField of the last record)

I only want to exclude a record with 0 when the previous record read (in order by company, asset, sequence desc) has the same company and asset

Order by to read in this order

2 ,                  5456    ,      2  ,                      1

2  ,                  5456    ,      1  ,                      1

2  ,                5456      ,    0    ,                    1                    --> 0 and same company, asset --> exclude

2    ,                10246      ,  0      ,                    1