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

    Header and detail in same file, separate rows



      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?




        • 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.



          LOAD @1 as RAWLINE, recno() as RecID



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

          ColSplit(1, IntArray()),

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

          Remove(Row, Pos(Top, 10))




          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;



          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.

              replace(@1,' ','|') as row
              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;
              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')

              $(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;



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


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


              DROP TABLES Data, Datatmp;