Qliksense peek 2 rows above

[beaubellamy]

I am trying to join the station 2 rows above with the current station in a load scrip, but it doesn't return the value for the previous station (2 rows above). I'm using peek(station,-2) after sorting the tables previously, so the rows are in the order that i expect.

I have confirmed that the order is correct by writing the sorted table to a csv for inspection, but when i reload the table using Resident and try to use peek to join the two stations, it doesn't work.

The order of the table will be

route_id: One of three values ['BTAN','FEST','GLNG' ]

current_trip: Unique identifier for each trip, there will be several hundred different alpha numeric values.

stop_seq: order the stations are visited, (integer)

arrival: one of two values: ['arrival','departure]

station: The station label.

temp:
LOAD *
FROM [lib://......./file.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

temp2:
NoConcatenate
LOAD *
Resident temp
Order By route_id, current_trip, stop_seq, arrival desc;

Drop Table temp;
store temp2 into [lib://test.csv](txt);  // I have confirmed the correct order of the data.

Qualify *;
NoConcatenate
NWS:
LOAD *,
if (arrival = 'departure' and stop_seq <= 2, station,
    if (arrival = 'arrival' and stop_seq <= 2, Peek(station), Peek(station,-2,'NWS')&'-'&station)) as section

Resident temp2;

Unqualify *;

Drop Table temp2;

arrival	current_trip	route_id	section_name	stop_seq	Expectation
departure	0000001-alphanumeric-01	BTAN	Station 1	2	Station 1
arrival	0000001-alphanumeric-01	BTAN	Station 2	2	Station 1
departure	0000001-alphanumeric-01	BTAN	Station 2	3	Station 1-Station 2
arrival	0000001-alphanumeric-01	BTAN	Station 3	3	Station 2-Station 3
departure	0000001-alphanumeric-01	BTAN	Station 3	4	Station 2-Station 3
arrival	0000001-alphanumeric-01	BTAN	Station 4	4	Station 3-Station 4
departure	0000001-alphanumeric-01	BTAN	Station 5	5	Station 3-Station 5
arrival	0000001-alphanumeric-01	BTAN	Station 5	5	Station 4-Station 5
departure	0000001-alphanumeric-01	BTAN	Station 6	6	Station 5-Station 6
arrival	0000001-alphanumeric-01	BTAN	Station 6	6	Station 5-Station 6

[Vegar]

You could try to put the order by in the LOAD containing the peek and use ' ' around the field name.

Qualify *;
NoConcatenate
NWS:
LOAD *,
if (arrival = 'departure' and stop_seq <= 2, station,
    if (arrival = 'arrival' and stop_seq <= 2, Peek('station'), Peek('station',-2,'NWS')&'-'&station)) as section

Resident temp2

Order By route_id, current_trip, stop_seq, arrival desc;

[Vegar]

Take a look att the attached qvw reading the data you posted above in the table. 

TMP:
LOAD * inline [
arrival,current_trip,route_id,section_name,stop_seq,Expectation
departure,0000001-alphanumeric-01,BTAN,Station 1,2,Station 1
arrival,0000001-alphanumeric-01,BTAN,Station 2,2,Station 1
departure,0000001-alphanumeric-01,BTAN,Station 2,3,Station 1-Station 2
arrival,0000001-alphanumeric-01,BTAN,Station 3,3,Station 2-Station 3
departure,0000001-alphanumeric-01,BTAN,Station 3,4,Station 2-Station 3
arrival,0000001-alphanumeric-01,BTAN,Station 4,4,Station 3-Station 4
departure,0000001-alphanumeric-01,BTAN,Station 5,5,Station 3-Station 5
arrival,0000001-alphanumeric-01,BTAN,Station 5,5,Station 4-Station 5
departure,0000001-alphanumeric-01,BTAN,Station 6,6,Station 5-Station 6
arrival,0000001-alphanumeric-01,BTAN,Station 6,6,Station 5-Station 6]

;
NoConcatenate
NWS:
LOAD *,
if (arrival = 'departure' and stop_seq <= 2, section_name,
    if (arrival = 'arrival' and stop_seq <= 2, Peek('section_name'), Peek('section_name',-2,'NWS')&'-'&section_name)) as section

Resident TMP
ORDER BY route_id, current_trip, stop_seq, arrival desc;
;

DROP TABLE TMP;