Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

3 Replies
swuehl
MVP
MVP

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;

Not applicable
Author

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.

flipside
Partner - Specialist II
Partner - Specialist II

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