Split Timestamp

[wunderch]

Hello,

I have a table like this:

Name	Time
Rob Thunder	06:13
Rob Thunder	09:00
Rob Thunder	09:20
Rob Thunder	12:00
Rob Thunder	13:00
Rob Thunder	16:00

And now I need a table like this:

Name	From	Until
Rob Thunder	06:13	09:00
Rob Thunder	09:20	12:00
Rob Thunder	13:00	16:00

Any Ideas, how I can make this?

Fieldvalue??

Rowno??

Kind regards

Chris

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD Name,

    Time,

    Ceil(RowNo()/2) as Sno

FROM

[https://community.qlik.com/thread/251403]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD Sno,

  Name,

  FirstValue(Time) as From,

  LastValue(Time) as Until

Resident Table

Group By Name, Sno;

DROP Table Table;

[sunny_talwar]

Or this if Time is not ordered correctly

Table:

LOAD Name,

    Time

FROM

[https://community.qlik.com/thread/251403]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD Sno,

  Name,

  FirstValue(Time) as From,

  LastValue(Time) as Until

Group By Name, Sno;

LOAD Name,

  Time,

    Ceil(RecNo()/2) as Sno

Resident Table

Order By Name, Time;

DROP Table Table;

[rahulpawarb]

Hello Christian,

Trust that you are doing great!

Please refer below given draft version of script for your reference:

Data:
LOAD *,
RowNo() AS RowNo;
LOAD * INLINE [
Name, Time
Rob Thunder, 06:13
Rob Thunder, 09:00
Rob Thunder, 09:20
Rob Thunder, 12:00
Rob Thunder, 13:00
Rob Thunder, 16:00
];

TmpData:
LOAD *,
     Peek(Time, -1) AS To
Resident Data
Order By RowNo DESC;

FinalData:
LOAD
     Name,
     Time AS From,
     To
Resident TmpData
where Mod(RowNo,2) = 1
Order By RowNo;

DROP Table Data, TmpData;

P.S.: Given script is designed considering below statements:

     1. From Time entry resides on Odd row no and To Time entry is available at Even row no.

     2. Every From record must have corresponding To records.

If base table contains data bypassing above said rules then change the data structure to identify From and To records. This will definitely help in further development process.

Regards!

Rahul

[wunderch]

Thanks for your quick answer!

That's what I'm looking for!! It works great!