Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Sort and store time based info

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)

   

CustomerRoomActionDate
John1IN01/01/2018
John3IN10/01/2018
John1OUT02/01/2018
Paul6OUT25/01/2018
John1IN22/01/2018
John2OUT07/01/2018
John3OUT15/01/2018
Paul4IN01/01/2018
Paul5IN06/01/2018
Paul6IN20/01/2018
Paul5OUT10/01/2018
Paul4OUT03/01/2018
Paul4IN26/01/2018
Paul4OUT30/01/2018
John2IN05/01/2018
John1IN20/01/2018

And I am trying to obtain this output.

 

CustomerRoomDate INDate OUT
John101/01/201802/01/2018
John205/01/201807/01/2018
John310/01/201815/01/2018
Paul401/01/201803/01/2018
Paul506/01/201810/01/2018
Paul620/01/201825/01/2018
John120/01/201822/01/2018
Paul426/01/201830/01/2018

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

Thanks a lot in advance.

 

1 Reply
Highlighted
MVP
MVP

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;

2018-06-05 18_59_21-QlikView x64 - Evaluation Copy - [C__Users_pts_Downloads_# QC 2018-06-05 Room In.png