topic Header and detail in same file, separate rows in QlikView

Header and detail in same file, separate rows

Wed, 25 Jan 2012 10:38:03 GMT

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

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

swuehl — Wed, 25 Jan 2012 12:43:45 GMT

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

Wed, 25 Jan 2012 13:24:55 GMT

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

flipside — Wed, 25 Jan 2012 13:42:09 GMT

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