Sort and store time based info

lfalmoguera — Sun, 03 Jun 2018 19:13:51 GMT

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.

Re: Sort and store time based info

petter — Tue, 05 Jun 2018 16:59:52 GMT

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;

topic Re: Sort and store time based info in QlikView

Sort and store time based info

Re: Sort and store time based info