FirstSortedValue issues

jmcdermott — Mon, 13 May 2024 21:12:45 GMT

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;

Re: FirstSortedValue issues

BrunPierre — Tue, 14 May 2024 21:14:45 GMT

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;

Re: FirstSortedValue issues

jmcdermott — Wed, 15 May 2024 14:24:10 GMT

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,

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;

topic Re: FirstSortedValue issues in QlikView

FirstSortedValue issues

Re: FirstSortedValue issues

Re: FirstSortedValue issues