Peek/Previous bringing back current and previous value

[steph_9131]

Hi,

Using QlikView and I need to bring back previous value from a table but it is currently bringing back the current value and duplicating the line for another record for the previous value

Load script below and what I have tried and the results and what I do need.

Temp_Table:
Load
[Bar Code],
TicketID,
[Request Date],
[Booked In Date]

From
$(QVDPath);

Final_Table:
Load
*,
If([Bar Code]=Previous([Bar Code]),Peek([Request Date],-1)) as [Previous Request Date]

Resident Temp_Table
Order By [Bar Code],
TicketID,
[Booked in Date];

Drop Table Temp_Table;

I have also used If([Bar Code]=Previous([Bar Code]), Previous([Request Date]),'') as [Previous Request Date] but it gives the same results as peek above.

What I would like:

Bar Code	Booked In	Request Date	Previous Request Date
A10112084	30-01-2017	22-02-2017	-
A10112084	25-07-2017	04-09-2017	22/02/2017
A10112084	13-11-2017	27-11-2017	04/09/2017
B2102985	28/12/2016	04/02/2017	-
B2102985	18/06/2017	09/08/2017	04/02/2017

However the previous request date comes back duplicating the row with the current date as well:

Bar Code	Booked In	Request Date	Previous Request Date
A10112084	30-01-2017	22-02-2017	22-02-2017
A10112084	30-01-2017	22-02-2017	-
A10112084	25-07-2017	04-09-2017	22-02-2017
A10112084	25-07-2017	04-09-2017	04-09-2017
A10112084	13-11-2017	27-11-2017	04-09-2017
B2102985	28-12-2016	04-02-2017	04-02-2017
B2102985	28-12-2016	04-02-2017	-
B2102985	18/06/2017	09/08/2017	04/02/2017

Note that the last value for the barcode only comes back with 1 value and not its current one as well which I'm not sure why.

Thanks

[smith11484]

Peek() and Previous() allow you to target defined rows within a table. The biggest difference between the two functions is that the Peek() function allows the user to look into a field that was not previously loaded into the script whereas the Previous() function can only look into a previously loaded field. Track Insurance Coverage

[PrashantSangle]

can you share some sample data with expected output??

Regards,

Prashant Sangle

[steph_9131]

Hi @PrashantSangle

Please find larger sample data attached. The first table of data above is a small sample as well. I need a Previous Request Date field that brings back the request date from the previous row where the barcode is the same as the previous row.

[MarcoWedel]

maybe one solution could be:

Temp_Table:
LOAD *, Alt([Booked In],[Request Date]) as AltSortDate;
LOAD [Bar Code], 
     Date#([Booked In],'DD-MM-YYYY') as [Booked In], 
     Date([Request Date],'DD-MM-YYYY') as [Request Date]
FROM [https://community.qlik.com/cyjdu72974/attachments/cyjdu72974/qlikview-app-development/1218967/1/Sample%20Data.xls] (biff, embedded labels, table is [Sample Data$]);

Final_Table:
LOAD *,
     If([Bar Code]=Previous([Bar Code]),Previous([Request Date])) as [Previous Request Date]
Resident Temp_Table
Order By [Bar Code], AltSortDate;

Drop Table Temp_Table;

hope this helps

Marco