Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Load and concat "broken" rows of data

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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.

View solution in original post

6 Replies
Anonymous
Not applicable
Author

Michael,

You can use subfield() function - see attached.

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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?


maxgro
MVP
MVP

File:

LOAD rowno() as RN, A;

LOAD

SubField(@1, '|') as A

FROM

file.txt

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

Anonymous
Not applicable
Author

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.

oleg_orlov
Creator
Creator

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)

    ))

;