Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

michaelk
Contributor

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
mov
Esteemed Contributor III

Re: Re: Load and concat "broken" rows of data

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.

6 Replies
mov
Esteemed Contributor III

Re: Load and concat "broken" rows of data

Michael,

You can use subfield() function - see attached.

michaelk
Contributor

Re: Load and concat "broken" rows of data

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

mov
Esteemed Contributor III

Re: Load and concat "broken" rows of data

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?


MVP
MVP

Re: Load and concat "broken" rows of data

File:

LOAD rowno() as RN, A;

LOAD

SubField(@1, '|') as A

FROM

file.txt

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

mov
Esteemed Contributor III

Re: Re: Load and concat "broken" rows of data

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
Contributor

Re: Load and concat "broken" rows of data

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)

    ))

;