Peek and nulls

[ManuelLindekeit]

Hello -

I'm looking for input on how to be write code around the peek function to to grab the last not null value. For ID = 3, peek(TimeStamp) will be null. But in this case, I want the peek to take TimeStamp from ID = 1,e.g. the next most recent no null value.

Thanks

Set NullInterpret = '';

A:
Load * Inline
[
TimeStamp, ID
5/25/2020 5:27:47 PM,1
,2
5/25/2020 6:27:47 PM,3
5/25/2020 8:27:47 PM,4
];

Q:
Load
ID,
date(TimeStamp),
num(date(TimeStamp)),
TimeStamp,
peek(TimeStamp,-1)
Resident A;

drop Table A;

[Lisa_P · Accepted solution]

Try this for Q:

Q:
Load *,
Date#(Subfield(NewTimeStamp, ' ', 1),'M/DD/YYYY') as DateString;
Load
ID,
TimeStamp,
If(IsNull(TimeStamp), Peek(NewTimeStamp), TimeStamp) as NewTimeStamp
Resident A;

[ManuelLindekeit]

Thanks Lisa. Follow up on peek()'s behavior. When I first looked at the IF statement, I thought it would work if the last loaded row is null but not if the two last loaded rows are null. I thought so because peek() assumes -1 if the second argument is left out. Why does the peek pick the last non-null timestamp instead of just peek(timestamp,-1)?

[ManuelLindekeit]

Also, I see that you used Peek(NewTimeStamp) on a line where you were
creating NewTiemStamp. I would have thought a preceding load would have
been needed. That's cool. Is that because peek() evaluates on internal
table, rather than source table?