Peek function

[alisonpwallis]

Hi

I was hoping someone could help me use Peek in my QV document.

I want to create a new field for my data using the peek function. Each record I have contains a date and a status. I want to create a new field which contains the date of the next record (for the same ID) as NextDate. So for example:

ID Date of Status Change Status

1234     1/2/2013               C

1234     9/5/2013               CF

1234     20/8/2013             UF

So what I want is to have a new field called NextDate which shows the date of status change for the next record:

ID Date of Status Change Status  NextDate

1234     1/2/2013               C        9/5/2013

1234     9/5/2013               CF      20/8/2013

1234     20/8/2013             UF      N/A

Can I use Peek to do this? I will presumably need to sort the file as well so that the records are in date order?

Attached is the qvw file that I am working with.

Thanks very much for your help

Alison

[MK_QSL · Accepted solution]

Temp:

Load * Inline

[

  ID, Date of Status, Change Status

  1234,    1/2/2013,          C

  1234,    9/5/2013,          CF

  1234,    20/8/2013,         UF

  5678,    1/2/2013,          M

  5678,    9/5/2013,          MN

  5678,    20/8/2013,         MNO

];

Final:

Load

  ID,

  [Date of Status],

  [Change Status],

  IF(ID = Previous(ID), Peek([Date of Status]), 'N/A') as NextDate

Resident Temp

Order By ID, [Date of Status] Desc;

Drop Table Temp;

[alisonpwallis]

That's great - exactly what I needed. Thank you so much!

Alison