Good morning BrunPierre,
I ended up using the code below and that fixed it.
NoConcatenate
famaster_mst_01:
LOAD
AffiliateCode & '|' & dept as AffiliateCode|dept,
Trim(AffiliateCode & '|' & fa_num) as AffiliateCode|fa_num, //changed from as AffiliateCode|fa_num,|Seq#
Trim(AffiliateCode & '|' & dept & '|' & fa_class) as AffiliateCode|dept|fa_class,
// Trim(AffiliateCode & '-' & dept & '-' & fa_class & '-' & type) as AffiliateCode|dept|fa_class|AssetType, //removed
AffiliateCode,
fa_num,
type,
fa_desc,
dept,
loc,
tag,
fa_class,
acq_date, // when they bought it not when depr starts
vend_num,
mfg,
model,
serial,
fa_stat,
life,
units_cur,
read_prior,
read_curr,
insur_val,
insur_vnd,
insur_pol#,
insur_exp,
inv_freq,
inv_date,
dispose_date,
dispose_amt,
review,
post_from_po,
NoteExistsFlag,
num(Floor( RecordDate)) as RecordDate, //changed to this from RecordDate,
RowPointer,
CreatedBy,
UpdatedBy,
CreateDate,
InWorkflow,
date_to_start_depr,
Uf_SplitAsset,
Uf_SplitAssetDate,
Uf_Ringi_Number
FROM
[..\QVD\famaster_mst.qvd]
(qvd)
Where
//AffiliateCode = 77048 and
fa_stat <>'D' and
Uf_SplitAsset <> 1 //and // Exclude 'D' for disposals and include 'A' for Active and 'I' for Inactive assets
//fa_num = 023653
;
//EXIT SCRIPT;
NoConcatenate
famaster_mst_02:
LOAD Distinct // we load Distinct because Distinct will only return one unique record if all fields are identical. With RecordDate, decription, and serial commented out all other fields were identical.
AffiliateCode|dept,
AffiliateCode|fa_num,
AffiliateCode|dept|fa_class,
// AffiliateCode|dept|fa_class|AssetType, // removed this
AffiliateCode,
fa_num,
type,
// fa_desc, // desc is pulled after a unique field is created with the date and fa_num
dept,
loc,
tag,
fa_class,
acq_date, // when they bought it not when depr starts
vend_num,
mfg,
model,
// serial, // Serial is pulled after a unique field is created with the date and fa_num
fa_stat,
life,
units_cur,
read_prior,
read_curr,
insur_val,
insur_vnd,
insur_pol#,
insur_exp,
inv_freq,
inv_date,
dispose_date,
dispose_amt,
review,
post_from_po,
NoteExistsFlag,
// RecordDate, we don't include this here it gets added in next table to create unique record.
RowPointer,
CreatedBy,
UpdatedBy,
CreateDate,
InWorkflow,
date_to_start_depr,
Uf_SplitAsset,
Uf_SplitAssetDate,
Uf_Ringi_Number
Resident
famaster_mst_01;
NoConcatenate
Desc_01:
LOAD
AffiliateCode|fa_num & '|' & RecordDate as AffiliateCode|fa_num|RecordDate, // make unique record of asset number and record date
AffiliateCode|fa_num,
fa_desc,
serial,
RecordDate
Resident
famaster_mst_01;
NoConcatenate
Desc_02:
LOAD
AffiliateCode|fa_num,
Max(RecordDate) as RecordDate_Max //gives us the most recent date
Resident
Desc_01
Group By
AffiliateCode|fa_num;
NoConcatenate
Desc_03:
LOAD
AffiliateCode|fa_num & '|' & RecordDate_Max as AffiliateCode|fa_num|RecordDate //gives us the unique asset with date most recent date and drops Desc_02 table
Resident
Desc_02;
DROP Table Desc_02;
Left Join(Desc_03) //joins the fields we need that are unique and drops Desc_01 and famaster_mst_01
LOAD
AffiliateCode|fa_num|RecordDate,
AffiliateCode|fa_num,
fa_desc,
serial
Resident
Desc_01;
DROP Table Desc_01;
DROP Table famaster_mst_01;
Left Join(famaster_mst_02) //joins the recent desc to famaster_mst_02 and drops Desc_03 now all extra Desc 01,02,03 are gone
LOAD
AffiliateCode|fa_num,
fa_desc,
serial
Resident
Desc_03;
DROP Table Desc_03;
//Exit SCRIPT;
famaster_mst:
LOAD
AffiliateCode|dept, // AffiliateCode & '|' & dept as AffiliateCode|dept,
AffiliateCode|fa_num,// Trim(AffiliateCode & '|' & fa_num) as AffiliateCode|fa_num|seq#,
AffiliateCode|dept|fa_class,// Trim(AffiliateCode & '|' & dept & '|' & fa_class) as AffiliateCode|dept|fa_class,
// AffiliateCode|dept|fa_class|AssetType, // Trim(AffiliateCode & '-' & dept & '-' & fa_class & '-') as AffiliateCode|dept|fa_class|AssetType, //removed
AffiliateCode,
fa_num,
// type,
fa_desc,
// dept,
loc,
tag,
// fa_class,
acq_date, // when they bought it not when depr starts
vend_num,
// mfg,
model,
serial,
fa_stat,
life,
// units_cur,
// read_prior,
// read_curr,
// insur_val,
// insur_vnd,
// insur_pol#,
// insur_exp,
// inv_freq,
// inv_date,
// dispose_date,
// dispose_amt,
// review,
// post_from_po,
// NoteExistsFlag,
// RecordDate,
RowPointer,
// CreatedBy,
// UpdatedBy,
// CreateDate,
// InWorkflow,
date_to_start_depr,
Uf_SplitAsset
// Uf_SplitAssetDate,
// Uf_Ringi_Number
Resident
famaster_mst_02;
DROP Table famaster_mst_02;
//Where
//fa_stat <>'D' and
//Uf_SplitAsset <> 1 //and // Exclude 'D' for disposals and include 'A' for Active and 'I' for Inactive assets
//fa_num = 412
//;
Left Join(famaster_mst) // left joining this eliminates the doubling of assets for each country
Load
AffiliateCode
Resident
GLPERIOD;