Last Goods In Date

[pkelly]

Hi All,

Looking for some assistance on how I would go about this.

We have a mac_cost table which contains transaction detail for all products - ins and outs.

I am trying to determine the last "in" transaction for each unique product id.

A unique product id is determined by concatenating the following fields together from this table...

loc & ‘-‘ & ”pro-type” & ‘-‘ & product & ‘-‘ & ”cost-id”

Their is also a field called "hist-seq" which, when sorted descending within the unique product id, will have more recent transactions first.

What I want to do is work my way through each unique product id and, if the type of transaction (”trans-doc-type”) is either RCV or WOP, extract the "date-stamp" field and move on to the  next record.

So will end up with the following for each unique product id...

Unique product id, date last in

I will be using this data on various reports.

Any assistance greatly appreciated.

Paul

[MK_QSL]

Provide some sample data to work !

[pkelly]

Unfortunately I cannot as data is too sensitive for my company.

[MK_QSL]

You can obviously create dummy date (say 20-30 lines) with similar field names !

[#5]

You can use FirstSortedValue.  Here is a small example:

Table:

Load * Inline [

ID, Recieved

CCC, 12/15/2015

ABC, 12/1/2015

AAA, 12/2/2015

BBB, 12/5/2015

CCC, 12/6/2015

ABC, 12/5/2015

AAA, 12/10/2015

];

Res:

Load

ID as ID2,

FirstSortedValue(Recieved, -Recieved) as LastIn

Resident Table Group by ID;

For your requirement, I would use the firstsortedvalue load as a mapping load, then use an 'if' based on type RCV or WOP to apply the map to your larger data set.

***EDIT I misread your conditional requirement. You will want to load only RCV and WOP records before you apply for firstsortedvalue, otherwise you will get out bound dates as well.  Then use the mapping to apply the last in dates to your larger set.

[pkelly]

Example data extract attached...

[MK_QSL]

Data:

LOAD loc & '|' & [pro-type] & '|' & product & '|' & [cost-id] as Key,

  loc,

     [pro-type],

     product,

     [cost-id],

     [hist-seq],

     [trans-doc-type],

     [Date-Stamp]

FROM

For Forum.xlsx

(ooxml, embedded labels, table is Sheet2);

Load Key, Date(Max([Date-Stamp])) as DateLastIn Resident Data

Where Match([trans-doc-type],'RCV','WOP')

Group By Key;

Drop Table Data;          //Drop only if you dont need this Data table

[#8]

Take a look at the attached.  I always hesitate to use Max in the script as it tends to slow things down quite a bit.

[maxgro]

Another one could be

Data:

LOAD loc & '|' & [pro-type] & '|' & product & '|' & [cost-id] as Key,

  loc,

    [pro-type],

    product,

    [cost-id],

    [hist-seq],

    [trans-doc-type],

    [Date-Stamp]

FROM

For Forum.xlsx

(ooxml, embedded labels, table is Sheet2);               // this is from Manish script

Load                                                                                  

  Key, [Date-Stamp] as DateLastIn

Resident Data

Where Match([trans-doc-type],'RCV','WOP') and Key <> Peek('Key')

Order By Key, [Date-Stamp] desc;