topic Re: Split Timestamp in QlikView

Split Timestamp

wunderch — Mon, 27 Feb 2017 10:22:13 GMT

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

Re: Split Timestamp

sunny_talwar — Mon, 27 Feb 2017 10:36:59 GMT

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;

Re: Split Timestamp

sunny_talwar — Mon, 27 Feb 2017 10:38:32 GMT

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;

Re: Split Timestamp

rahulpawarb — Mon, 27 Feb 2017 10:42:05 GMT

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

Re: Split Timestamp

wunderch — Mon, 27 Feb 2017 13:25:55 GMT

Thanks for your quick answer!

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