Sort and store time based info

[lfalmoguera]

Hi all,

I am struggling in order to sort some information and store it based on time and action.

Here is a easy example of what I am trying to do.

I have this information (not sorted)

Customer	Room	Action	Date
John	1	IN	01/01/2018
John	3	IN	10/01/2018
John	1	OUT	02/01/2018
Paul	6	OUT	25/01/2018
John	1	IN	22/01/2018
John	2	OUT	07/01/2018
John	3	OUT	15/01/2018
Paul	4	IN	01/01/2018
Paul	5	IN	06/01/2018
Paul	6	IN	20/01/2018
Paul	5	OUT	10/01/2018
Paul	4	OUT	03/01/2018
Paul	4	IN	26/01/2018
Paul	4	OUT	30/01/2018
John	2	IN	05/01/2018
John	1	IN	20/01/2018

And I am trying to obtain this output.

Customer	Room	Date IN	Date OUT
John	1	01/01/2018	02/01/2018
John	2	05/01/2018	07/01/2018
John	3	10/01/2018	15/01/2018
Paul	4	01/01/2018	03/01/2018
Paul	5	06/01/2018	10/01/2018
Paul	6	20/01/2018	25/01/2018
John	1	20/01/2018	22/01/2018
Paul	4	26/01/2018	30/01/2018

Any idea how to cope with it? I am geeting mad : (

Thanks a lot in advance.

[petter]

You can simply do it in the load script like this:

DATA:

LOAD * INLINE [

Customer Room Action Date

John 1 IN 01/01/2018

John 3 IN 10/01/2018

John 1 OUT 02/01/2018

Paul 6 OUT 25/01/2018

John 1 OUT 22/01/2018

John 2 OUT 07/01/2018

John 3 OUT 15/01/2018

Paul 4 IN 01/01/2018

Paul 5 IN 06/01/2018

Paul 6 IN 20/01/2018

Paul 5 OUT 10/01/2018

Paul 4 OUT 03/01/2018

Paul 4 IN 26/01/2018

Paul 4 OUT 30/01/2018

John 2 IN 05/01/2018

John 1 IN 20/01/2018

] (delimiter is \t)

;

QUALIFY *;

[']:

LOAD

  Customer,

  Room,

  Previous(Date) AS InDate,

  Date AS OutDate

RESIDENT

  DATA

WHERE

   Action='OUT'

ORDER BY

  Customer,Date,Action;