Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a text file with the followin structure:
2014-01-27 00:04:18 Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua.| 2014-01-27 00:04:19 At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum| 2014-01-27 00:04:20 dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr,| 2014-01-27 00:04:21 sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua.
In reallity it is a collection of data with words and numbers with a date and time information at the beginning of each line. The problem is, that each line above is a value of the field. The actual lines with the complete information is separated by a |.
How can I read the lines and concat the right parts to a complete line with the date and time at the beginning like:
Row1: 2014-01-27 00:04:18 Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua.
Row2: 2014-01-27 00:04:19 At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum
Row3: 2014-01-27 00:04:20 dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr,
Row4: 2014-01-27 00:04:21 sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua.
Thanks a lot!
Michael
OK, no answer means "yes"...
See another version. I concatenate the pieces in one field. A inor complication was to keep the correct order when concatenating - used a field for this based on RowNo().
The rest is the same.
To minor improvements - used trim() when creating Newfield, and used sort order on the front end based on the timestamp extracted from the new field start.
Michael,
You can use subfield() function - see attached.
Thanks Michael,
but this would have been to easy. In fact the above text consists of different rows.
Row1: 2014-01-27 00:04:18 Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod Row2: tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua.| 2014-01-27 00:04:19 At vero Row3: eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Row4: Lorem ipsum| 2014-01-27 00:04:20 dolor sit amet. Lorem ipsum dolor sit amet, consetetur sadipscing elitr,| Row5: 2014-01-27 00:04:21 sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Row6: sed diam voluptua.
So I have to take the complete row1 and the part of row2 up to the separator | to result in the actual row (e.g.)!
Sorry for being not precise enough.
Michael
I guess that I got the end result correctly, but the source data in my example is simplified - in reality it has five rows and rows are not separated correctly? Is it the case?
File:
LOAD rowno() as RN, A;
LOAD
SubField(@1, '|') as A
FROM
file.txt
(txt, codepage is 1252, no labels, delimiter is '\t', msq);
OK, no answer means "yes"...
See another version. I concatenate the pieces in one field. A inor complication was to keep the correct order when concatenating - used a field for this based on RowNo().
The rest is the same.
To minor improvements - used trim() when creating Newfield, and used sort order on the front end based on the timestamp extracted from the new field start.
I think Michael's solution is right.
Also you can use standart possibility of QlikView to apply some conversions to the loading file. When you load data from the delimited file you can choose the delimeter in select box (<Custom...> -> '|'). After that you can click next and click "Enable Transformation Step" - if you choose Rotate tab you may rotate table to the right.
Table:
Load
Item
, Timestamp(Left(Item, 19)) As DateTime
, Mid(Item, 20) As Text
;
Load
Trim(@1) As Item
From
[file.txt](txt, codepage is 1251, no labels, delimiter is '|', msq, filters(
Rotate(right)
))
;