Problem with duplicate data

Wed, 17 Dec 2014 15:51:55 GMT

I am not sure why I am getting duplicate data but I suspect it has something to do with my load process.

I have an asset table which lists all assets. There is only one assetId for each asset.

I also have a book_parts table that contains assetId & sequence. There could be multiple sequences for the same asset.

I need to load the book_parts table and look at the contents to create a new table and then merge that with the asset table.

If the book_parts table has multiple sequences for the same asset then it must eliminate the sequence that is 0 and pull the others but if there is only a sequence 0 then it just needs to keep that.

When I try to create a pivot or straight table it looks like the assetId & sequence is listed twice like there is another table.

Here is my load info:

assets:

LOAD CompanyID,

630 as Parent_Comp,

AssetID,

Description,

AcquisitionDate,

Year(AcquisitionDate) as Fiscal_Year,

Month(AcquisitionDate) as Fiscal_Period,

if(IsInactive='F','Active','Inactive') as Status,

ReplaceValCat;

SQL SELECT *

FROM Asset

where CompanyID=2 and

(AssetGL<>00000877 or AssetGL<>00000949 or AssetGL<>00000954 or AssetGL<>00000955);

Concatenate

LOAD CompanyID,

670 as Parent_Comp,

AssetID,

Description,

AcquisitionDate,

Year(AcquisitionDate) as Fiscal_Year,

Month(AcquisitionDate) as Fiscal_Period,

if(IsInactive='F','Active','Inactive') as Status,

ReplaceValCat;

SQL SELECT *

FROM Asset

where CompanyID=3;

book_parts1:

NoConcatenate

LOAD CompanyID,

AssetID,

CompanyID & '-' & AssetID as NewField,

Sequence,

BookID,

PlaceServ,

AcqValue;

SQL SELECT CompanyID,

AssetID,

Sequence,

BookID,

PlaceServ,

AcqValue

FROM BookParts

where BookID=1;

book_parts:

NoConcatenate

LOAD * Resident

book_parts1

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

order by CompanyID, AssetID, Sequence desc;

DROP Table book_parts1;

Left join(assets)

LOAD * Resident

book_parts

Here's how my data looks in the table:

Why does it have the asset & sequence listed twice????????

Re: Problem with duplicate data

Gysbert_Wassenaar — Wed, 17 Dec 2014 19:52:00 GMT

Maybe because they exist for both companies? Check your data, because we cannot.

Re: Problem with duplicate data

Wed, 17 Dec 2014 20:43:09 GMT

This assetid is only in one company.

topic Re: Problem with duplicate data in QlikView

Problem with duplicate data

Re: Problem with duplicate data

Re: Problem with duplicate data