FirstSortedValue issues

[jmcdermott]

I am trying to pull my companies fixed assets and it is extremely over stated.  First issue is it is doubling up assets for multiple countries USA and Canada.  I have added AffiliateCode = 77048 to the Where clause and that seems to have fixed that issue but our fiscal year ends in 03/31/YY. Before the year rolled over this wasn't an issue.  

The 2nd issue is there are 2 record dates on some assets and I am trying to pull the newest date only.  I am trying to use FirstSortedValue with a minus weight to get the "last value".  Below is the code I am using.  What am I doing wrong?

NoConcatenate

famaster_mst:

LOAD 

AffiliateCode & '|' & dept as AffiliateCode|dept, 

Trim(AffiliateCode & '|' & fa_num) as AffiliateCode|fa_num|seq#,

Trim(AffiliateCode & '|' & dept & '|' & fa_class) as AffiliateCode|dept|fa_class,

Trim(AffiliateCode & '-' & dept & '-' & fa_class & '-') as AffiliateCode|dept|fa_class|AssetType,

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, 

     FirstSortedValue(fa_num, -RecordDate) as RecordDate,

    // 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 = 412

;

Exit SCRIPT;

[BrunPierre · Accepted solution]

FirstSortedValue() in the script is used with a group by clause to specify the context within which the function should operate.

Try like this.

Inner Join (famaster_mst)
/*--Max Record Date---*/
LOAD fa_num,
Max(RecordDate) as RecordDate
Resident Table
Group By fa_num;

[jmcdermott]

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;