6 Replies Latest reply: Jan 28, 2014 10:39 AM by Oleg Orlov RSS

    Load and concat "broken" rows of data

    Michael Kühn

      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

        • Re: Load and concat "broken" rows of data
          Michael Solomovich

          Michael,

          You can use subfield() function - see attached.

            • Re: Load and concat "broken" rows of data
              Michael Kühn

              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

                • Re: Load and concat "broken" rows of data
                  Michael Solomovich

                  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?


                    • Re: Load and concat "broken" rows of data
                      Oleg Orlov

                      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)

                          ))

                      ;

                    • Re: Re: Load and concat "broken" rows of data
                      Michael Solomovich

                      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.

                  • Re: Load and concat "broken" rows of data
                    Massimo Grossi

                    File:

                    LOAD rowno() as RN, A;

                    LOAD

                    SubField(@1, '|') as A

                    FROM

                    file.txt

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