Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Cheers Andrew, I just love it, With some small changes everything is Connected.
Thanks!
Hi Staffan,
Very pleased it works for you.
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
Thanks Marco.
That is a neat Piece of script which also Works fine.
Hi Marco,
Your script is a sparkling poem to my dull prose.
Regards
Andrew
marcowedel showing us a top notch script