Change Data format

[alexandermllr]

Hello,

I have this data format:

Key	Time	Start	End
12345	04.01.2017 13:09:46	02.01.2017 23:30:00
12345	05.01.2017 12:11:17		04.01.2017 16:15:00
12345	06.01.2017 08:03:13	05.01.2017 19:15:00
12345	10.01.2017 10:04:24		09.01.2017 05:45:00
12345	10.01.2017 10:04:48	09.01.2017 08:15:00	09.01.2017 15:45:00
12345	10.01.2017 10:05:07	09.01.2017 19:00:00
12345	16.01.2017 08:28:21		12.01.2017 05:45:00
12345	16.01.2017 08:51:28	13.01.2017 18:00:00	16.01.2017 05:45:00
12345	19.01.2017 08:28:24	16.01.2017 19:15:00

I need the Table like this:

Key	Time	Start	End
12345	04.01.2017 13:09:46	02.01.2017 23:30:00	04.01.2017 16:15:00
12345	05.01.2017 12:11:17
12345	06.01.2017 08:03:13	05.01.2017 19:15:00	09.01.2017 05:45:00
12345	10.01.2017 10:04:24
12345	10.01.2017 10:04:48	09.01.2017 08:15:00	09.01.2017 15:45:00
12345	10.01.2017 10:05:07	09.01.2017 19:00:00	12.01.2017 05:45:00
12345	16.01.2017 08:28:21
12345	16.01.2017 08:51:28	13.01.2017 18:00:00	16.01.2017 05:45:00
12345	19.01.2017 08:28:24	16.01.2017 19:15:00	date(today())

So if the field "End" isnull, then it should take the data from the next line from "End".

And if there is no next line in the field "End" it should take the data from today.

And grouped by the field "Key".

I hope someone can help me.

Thanks!

[swuehl · Accepted solution]

Maybe something like

SET TIMESTAMPFORMAT = 'DD.MM.YYYY hh:mm:ss';

Input:

LOAD Key,

     Time,

     Start,

     End

FROM

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

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

Result:

NoConcatenate

LOAD Key, Time, Start, If(Start,If(Previous(Key)=Key and Len(Trim(End))=0, Alt(Previous(End),Now(1)), Timestamp(Alt(End,Now(1))))) as End

Resident Input

Order By Key, Time desc;

DROP TABLE Input;

Key	Time	Start	End
12345	04.01.2017 13:09:46	02.01.2017 23:30:00	04.01.2017 16:15:00
12345	05.01.2017 12:11:17
12345	06.01.2017 08:03:13	05.01.2017 19:15:00	09.01.2017 05:45:00
12345	10.01.2017 10:04:24
12345	10.01.2017 10:04:48	09.01.2017 08:15:00	09.01.2017 15:45:00
12345	10.01.2017 10:05:07	09.01.2017 19:00:00	12.01.2017 05:45:00
12345	16.01.2017 08:28:21
12345	16.01.2017 08:51:28	13.01.2017 18:00:00	16.01.2017 05:45:00
12345	19.01.2017 08:28:24	16.01.2017 19:15:00	27.01.2017 10:24:41

[alexandermllr]

Thank you very much!