3 Replies Latest reply: Jan 25, 2012 8:42 AM by Dave Riley RSS

    Header and detail in same file, separate rows

      Hi!

       

      I have a file with data and we can say that one row is a header and next is details (if certain conditions is met). Now I need to assosiate them because I need information from both rows.

      Tried looping through and various transfoming, peek and fieldvalue but no success.

       

      This is a small number of the actual rows:

       

      4600424471000000100015512410000061721000006172035752      V2110  20120242012024
      4600424471000000100015512410000061721000006172035752      V2110  20120242012024
      4600424471000000100015512410000061721000006172035752      V2110  20120242012024
      46004244710000001000155124100000617210000061720357527522  V4120  20120242012024
      4601                                                                       000000025264403D
      4600424471000000100015512410000061721000006172035752   2  V4130  20
      4601       000000000000027               000000000712464DB000000000014231CR000000000000000
      4600424471000000100015512410000061721000006172035752   2  V4130  20120242012024
      4601       000000000000077               000000008366215DB000000000098311CR000000000000000
      
      

       

      Rows begining with 4600 is header and 4601 is details. Only headers with details is valid (a 4601 right under a 4600).

       

      Any suggestions?

       

      regards

      thomas

        • transform excel or in load script
          Stefan Wühl

          Not sure if this is what you are looking for, below code should result in a table where you have only valid (records with header and details) filtered and new category flags Header and Details set.

           

          RAW:

          LOAD @1 as RAWLINE, recno() as RecID

          FROM

          [http://community.qlik.com/thread/45946?tstart=0]

          (html, codepage is 1252, no labels, table is @1, filters(

          ColSplit(1, IntArray()),

          Remove(Row, Pos(Top, 11)),

          Remove(Row, Pos(Top, 10))

          ));

           

          TMP_RESULT:

          LOAD RecID, RAWLINE, if(Left(RAWLINE,4)=4601,1) as Details, if(Left(RAWLINE,4)=4600 and previous(Left(RAWLINE,4)=4601),1) as Header resident RAW order by RecID desc;

           

          RESULT:

          NOCONCATENATE LOAD RAWLINE, Header, Details, if(Rowno()=1,1, if(Header=1, peek(RecID)+1,peek(RecID))) as RecID resident TMP_RESULT where Details=1 or Header=1 order by RecID asc;

           

          drop table TMP_RESULT,RAW;

            • Re: transform excel or in load script

              Thanks, I just turned the problem and load all previous to current row and then find the ones that are correct.

              s:
              load 
              replace(@1,' ','|') as row
              FROM
              
              
              _tmp:
              LOAD
              Previous(left(row,4)) as trans4600,
              Previous(mid(row,5,6)) as xxxxx,
              left(row,4)   as trans4601,
              mid(row,5,7)  as yyyyyy
              Resident s;
              
              
              vss:
              NoConcatenate
              LOAD *
              Resident vss_tmp
              Where trans4600=4600 and trans4601=4601;
              
              
              DROP Table s;
              DROP Table _tmp;
              
              

              I'll try your solution as well.

              Changed title of post to make it easy/er to find.

            • Header and detail in same file, separate rows
              Dave Riley

              You could use the loop as follows which puts the header rows into a header table and row data into a rows table, linking on HdrId.  Datatmp is the name of the inline table I used for testing - it just has the raw data in it.

               

              For i = 1 to NoOfRows('Datatmp')

              Data:
              LOAD
              $(i) as RecordId,
              RawData as InspectedData,
              if(left(RawData,4) = 4600, 'H', 'D') as DataType,
              if(left(RawData,4) = 4600, $(i), peek(HdrId)) as HdrId
              resident Datatmp where RecNo()=$(i);


              next i;

               

               

              Headers:
              NOCONCATENATE LOAD HdrId, InspectedData as HeaderInfo resident Data where DataType='H'
              and left(Lookup('InspectedData','RecordId',RecordId+1,'Data'),4) <> 4600;

               

              Rows:
              NOCONCATENATE LOAD RecordId, InspectedData as RowData, HdrId resident Data where DataType='D';

               

              DROP TABLES Data, Datatmp;

               

               

              flipside