Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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;
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')
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