peek function

[maarcus_smith]

hi,

im using the peek function to grab the max date of the previous order and comparing this against the min date of the next order. it seems to work for some orders but not for others. im grouping my data by order, part and line,  in a previous table, then im resident loading that table in again to get the "ChangeoverTime"to give the user flexibility if they want to select a specific part or line. 

my code is:

final1:

min(SGRP.MinDate) as Min.Id, max(SGRP.MaxDate) as Max.Id
order, line , part
from x
group by order, line , part

final2:

 Min.Id - peek('Max.Id') as ChangeoverTime,
Min.Id, Max.Id, PART, order, line.

resident final1

drop table final1;

what i want from my output is that a user can select a line and/or part, and it will show them all the changeover time from the min of one batch, to the max of the previous batch for that part or line.

this is what im getting:

as you can see, most of the lines are correct, however the highlighted one is wrong. the changeover time should be null for this field as there is no previous order on that line, however, its taking the max id of the order on line 10 for some reason.

how do i correct this to where for the first order on each line or part, the changeover is null?

many thanks 

[rwunderlich]

If(line = peek('line'), Min.Id - peek('Max.Id'), null()) as ChangeoverTime

-Rob

[maarcus_smith]

@rwunderlich  hi rob thanks for answering me.

ive tried updating my peek function with your adivce, however it doesn't seem to have the desired affect, it makes some of my changeover times nulls but then there are still some minus'. however there are minus' not because of the line changing and i dont understand why, see the  highlighted rows 

[marcus_sommer]

You need also to add a proper order by statement to your resident-load to ensure that the order of the records are sorted in your intended way.

- Marcus