topic Peek/Previous bringing back current and previous value in QlikView

Peek/Previous bringing back current and previous value

steph_9131 — Fri, 13 May 2022 11:12:19 GMT

Hi,

Using 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

Re: Peek/Previous bringing back current and previous value

PrashantSangle — Sat, 14 May 2022 06:35:06 GMT

can you share some sample data with expected output??

Regards,

Prashant Sangle

Re: Peek/Previous bringing back current and previous value

steph_9131 — Mon, 16 May 2022 08:39:43 GMT

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.

Re: Peek/Previous bringing back current and previous value

MarcoWedel — Sun, 05 Jun 2022 10:16:17 GMT

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