Load previous / peek ???

[Anonymous]

Hi all,

I have the following table (this is a reduced table in reality this table has more columns):

ServiceOrders
Serviceordernr	Orderdate	Serialnumber	Resource
REP939444	13-11-2009	123-456-789	A
REP946431	11-01-2010	123-456-789	B
REP950330	04-02-2010	123-456-789	A

We are looking for repeated service orders for the same serial number. It is a repeated serviceorder if the orderdate is less than 6 months ago.

ServiceOrderRepeat:
LOAD *,
IF (
 ([Serialnumber] = previous ([Serialnumber])) and
 Serviceordernr <> previous(Serviceordernr) and
 (addmonths(Orderdate_origineel, -6) < previous (Orderdate)),
 1,0) as RepeatCounter,
LOAD
Serviceordernr,
Serialnumber,
Orderdate
RESIDENT ServiceOrders
ORDER BY Serialnumber, Serviceordernr, Orderdate;

This results in:

ServiceOrderRepeat
Serviceordernr	Orderdate	Serialnumber	RepeatCounter
REP939444	13-11-2009	123-456-789	0
REP946431	11-01-2010	123-456-789	1
REP950330	04-02-2010	123-456-789	1

But I also want a counter the other way around, I want to see the following

ServiceOrderRepeat
Serviceordernr	Orderdate	Serialnumber	RepeatCounter	RepeatCounter 2
REP939444	13-11-2009	123-456-789	0	1
REP946431	11-01-2010	123-456-789	1	1
REP950330	04-02-2010	123-456-789	1	0

I also want to have the sum of the last 6 months when selecting a month.

e.g. I select february, I want the REP950330 to have a total repeat counter of 2 ... at this moment I only have 1 ... but I need the sum of the last 6 months for the Serviceordernr.

Anyone has suggestions?

[Anonymous]

Did try to use Peek(Serialnumber , 1) to see if the next item had the same serial number to retrieve the RepeatCounter 2 but this gives an empty value ...

Any ideas??

[Anonymous · Accepted solution]

Fixed this using the following join with desc load
<blockquote><pre>
ServiceOrderRepeat:
LOAD *,
IF (
 ([Serialnumber] = previous ([Serialnumber])) and
 Serviceordernr <> previous(Serviceordernr) and
 (addmonths(Orderdate_origineel, -6) < previous (Orderdate)),
 1,0) as RepeatCounter,
LOAD
Serviceordernr,
Serialnumber,
Orderdate
RESIDENT ServiceOrders
ORDER BY Serialnumber, Orderdate, Serviceordernr;
JOIN

LOAD *,
IF (
 ([Serialnumber] = previous ([Serialnumber])) and
 Serviceordernr <> previous(Serviceordernr) and
 (addmonths(Orderdate_origineel, -6) < previous (Orderdate)),
 1,0) as RepeatCounter,
LOAD
Serviceordernr,
Serialnumber,
Orderdate
RESIDENT ServiceOrders
ORDER BY Serialnumber, Orderdate DESC, Serviceordernr;