topic Re: Peek function in QlikView

Peek function

alisonpwallis — Wed, 19 Mar 2014 16:41:24 GMT

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

Re: Peek function

MK_QSL — Wed, 19 Mar 2014 16:49:27 GMT

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;

Re: Peek function

alisonpwallis — Wed, 19 Mar 2014 17:18:41 GMT

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

Alison