topic Re: Last Goods In Date in QlikView

Last Goods In Date

pkelly — Fri, 22 Jan 2016 14:51:16 GMT

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

Re: Last Goods In Date

MK_QSL — Fri, 22 Jan 2016 14:52:41 GMT

Provide some sample data to work !

Re: Last Goods In Date

pkelly — Fri, 22 Jan 2016 14:55:20 GMT

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

Re: Last Goods In Date

MK_QSL — Fri, 22 Jan 2016 14:57:14 GMT

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

Re: Last Goods In Date

Fri, 22 Jan 2016 15:27:33 GMT

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.

Re: Last Goods In Date

pkelly — Fri, 22 Jan 2016 15:30:40 GMT

Example data extract attached...

Re: Last Goods In Date

MK_QSL — Fri, 22 Jan 2016 15:38:32 GMT

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

Re: Last Goods In Date

Fri, 22 Jan 2016 15:51:46 GMT

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.

Re: Last Goods In Date

maxgro — Fri, 22 Jan 2016 18:17:05 GMT

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;