Connect unstructed data

[stabben23]

Hi, This is my rawdata. What I need to do is to Connect the H-row With the S-rows.

H-row is an aggregated Level of S-rows(details). I need in some way add ex 17062807211706282341(which is the start and enddate) to the S-rows so I have a key between them.

All "Blocks" is separated With an "empty row", then next "Block" With a New start-end date 17062823411706291502 comes With Associated S-rows. You dont need to be aware of the data itself, just a way to Connect the H-row With S-rows.

PS all Blocks does not have common S-rows, sometimes its more or less then 10 as in the example.

H17062807211706282341100000002522243501528090001064160028193
S00010001000001600000750004421540000000000565149200333880000
S00020001000001600000750004180420000000000748139100351450000
S00030001000001600001000004421540000000000520349000408000000
S00040001000001600001000004180420000000000686839300432450000
S00050001000002500000750004180540000000000000844200000660000
S00060001000002500001000004180540000000000000245000000230000
S00070001000002500001250004180540000000000000136000000110000
S00100001000003200001000004180540000000000000547400000760000
S00130001000003800001000004180540000000000000243500000330000
S00150001000003800001500004180540000000000000139000000220000

H17062823411706291502100000002251142801307550001244210056041
S00010001000001600000750004421540000000000543148600317180000
S00020001000001600000750004180420000000000838439100393570000
S00030001000001600001000004421540000000000338948500263520000
S00040001000001600001000004180420000000000528939000330390000
S00090001000003200000750004180540000000000000351000000370000
S00100001000003200001000004180540000000000000744100000990000
S00120001000003800000750004180540000000000000343000000370000
S00130001000003800001000004180540000000000000148000000180000
S00150001000003800001500004180540000000000000243500000500000
S00170001000005000001000004180540000000000000248000000480000

[effinty2112 · Accepted solution]

Hi Staffan,

Try:

Key:

LOAD

AutoNumber(@1,'AutoKeyID') as AutoKeyID,

@1 as KeyLine

FROM

Rawdata.txt

(txt, codepage is 1252, no labels, delimiter is '\t', msq)

Where Left(@1,1) = 'H';

[Raw Data]:

LOAD @1 as Line

FROM

Rawdata.txt

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

For i = 1 to NoOfRows('Raw Data')

if Left(FieldValue('Line',$(i)) ,1) = 'H' THEN

Let vHKey =AutoNumber(FieldValue('Line',$(i)),'AutoKeyID');

END If;

AllData:

LOAD

FieldValue('Line',$(i)) as  Line,

'$(vHKey)' as AutoKeyID

AutoGenerate 1;

Next i;

NoConcatenate

Data:

LOAD * Resident AllData Where Left(Line,1) <> 'H';

DROP tables [Raw Data],AllData;

You get these tables:

Key:

AutoKeyID	KeyLine
1	H17062807211706282341100000002522243501528090001064160028193
2	H17062823411706291502100000002251142801307550001244210056041

Data:

AutoKeyID	Line
1	S00010001000001600000750004421540000000000565149200333880000
1	S00020001000001600000750004180420000000000748139100351450000
1	S00030001000001600001000004421540000000000520349000408000000
1	S00040001000001600001000004180420000000000686839300432450000
1	S00050001000002500000750004180540000000000000844200000660000
1	S00060001000002500001000004180540000000000000245000000230000
1	S00070001000002500001250004180540000000000000136000000110000
1	S00100001000003200001000004180540000000000000547400000760000
1	S00130001000003800001000004180540000000000000243500000330000
1	S00150001000003800001500004180540000000000000139000000220000
2	S00010001000001600000750004421540000000000543148600317180000
2	S00020001000001600000750004180420000000000838439100393570000
2	S00030001000001600001000004421540000000000338948500263520000
2	S00040001000001600001000004180420000000000528939000330390000
2	S00090001000003200000750004180540000000000000351000000370000
2	S00100001000003200001000004180540000000000000744100000990000
2	S00120001000003800000750004180540000000000000343000000370000
2	S00130001000003800001000004180540000000000000148000000180000
2	S00150001000003800001500004180540000000000000243500000500000
2	S00170001000005000001000004180540000000000000248000000480000

Regards

Andrew

[stabben23]

Cheers Andrew, I just love it, With some small changes everything is Connected.

Thanks!

[effinty2112]

Hi Staffan,

Very pleased it works for you.

[MarcoWedel]

Hi,

another solution might be:

tabTemp:
LOAD RangeSum(Peek(ID),-([@1:n] like 'H*')) as ID,
    Left([@1:n],1) as Type,
    [@1:n] as Line
FROM [https://community.qlik.com/servlet/JiveServlet/download/1331514-293096/Rawdata.txt] (fix, codepage is 1252)
Where Len([@1:n]);

table1:
Generic LOAD * Resident tabTemp;

DROP Table tabTemp;

hope this helps

regards

Marco

[stabben23]

Thanks Marco.

That is a neat Piece of script which also Works fine.

[effinty2112]

Hi Marco,

Your script is a sparkling poem to my dull prose.

Regards

Andrew

[stabben23]

marcowedel showing us a top notch script