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(
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;
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.
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);
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;