Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

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.

3 Replies
MVP
MVP

transform excel or in load script

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

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.

flipside
Valued Contributor II

Header and detail in same file, separate rows

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

Community Browser