Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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????????
