Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
alexandermllr
New Contributor II

Change Data format

Hello,

I have this data format:

KeyTimeStart

End

1234504.01.2017 13:09:4602.01.2017 23:30:00
1234505.01.2017 12:11:1704.01.2017 16:15:00
1234506.01.2017 08:03:1305.01.2017 19:15:00
1234510.01.2017 10:04:2409.01.2017 05:45:00
1234510.01.2017 10:04:4809.01.2017 08:15:0009.01.2017 15:45:00
1234510.01.2017 10:05:0709.01.2017 19:00:00
1234516.01.2017 08:28:2112.01.2017 05:45:00
1234516.01.2017 08:51:2813.01.2017 18:00:0016.01.2017 05:45:00
1234519.01.2017 08:28:2416.01.2017 19:15:00

I need the Table like this:

KeyTimeStartEnd
1234504.01.2017 13:09:4602.01.2017 23:30:00 04.01.2017 16:15:00
1234505.01.2017 12:11:17
1234506.01.2017 08:03:1305.01.2017 19:15:00 09.01.2017 05:45:00
1234510.01.2017 10:04:24
1234510.01.2017 10:04:4809.01.2017 08:15:0009.01.2017 15:45:00
1234510.01.2017 10:05:0709.01.2017 19:00:0012.01.2017 05:45:00
1234516.01.2017 08:28:21
1234516.01.2017 08:51:2813.01.2017 18:00:0016.01.2017 05:45:00
1234519.01.2017 08:28:2416.01.2017 19:15:00date(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!

1 Solution

Accepted Solutions
MVP
MVP

Re: Change Data format

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
1234504.01.2017 13:09:4602.01.2017 23:30:0004.01.2017 16:15:00
1234505.01.2017 12:11:17  
1234506.01.2017 08:03:1305.01.2017 19:15:0009.01.2017 05:45:00
1234510.01.2017 10:04:24  
1234510.01.2017 10:04:4809.01.2017 08:15:0009.01.2017 15:45:00
1234510.01.2017 10:05:0709.01.2017 19:00:0012.01.2017 05:45:00
1234516.01.2017 08:28:21  
1234516.01.2017 08:51:2813.01.2017 18:00:0016.01.2017 05:45:00
1234519.01.2017 08:28:2416.01.2017 19:15:0027.01.2017 10:24:41
2 Replies
MVP
MVP

Re: Change Data format

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
1234504.01.2017 13:09:4602.01.2017 23:30:0004.01.2017 16:15:00
1234505.01.2017 12:11:17  
1234506.01.2017 08:03:1305.01.2017 19:15:0009.01.2017 05:45:00
1234510.01.2017 10:04:24  
1234510.01.2017 10:04:4809.01.2017 08:15:0009.01.2017 15:45:00
1234510.01.2017 10:05:0709.01.2017 19:00:0012.01.2017 05:45:00
1234516.01.2017 08:28:21  
1234516.01.2017 08:51:2813.01.2017 18:00:0016.01.2017 05:45:00
1234519.01.2017 08:28:2416.01.2017 19:15:0027.01.2017 10:24:41
alexandermllr
New Contributor II

Re: Change Data format

Thank you very much!