topic Re: Exclude records from a previous load in QlikView

Exclude records from a previous load

Wed, 03 Dec 2014 20:45:31 GMT

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.

Re: Exclude records from a previous load

maxgro — Wed, 03 Dec 2014 21:04:54 GMT

RESULT

SCRIPT

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

];

NoConcatenate

load *

Resident a

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

order by Companyid , Assetid , Sequence desc ;

drop table a;

Re: Exclude records from a previous load

Wed, 03 Dec 2014 21:12:27 GMT

How does the peek work????

Re: Exclude records from a previous load

maxgro — Wed, 03 Dec 2014 21:25:18 GMT

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