How to create records from this file structure?

[#1]

Hello,

I need to create records from linenumber 115 to 123, how do I do this as a script?

see the attached file.

Håkan

[#2]

Load Record

from delivery.txt

where SNo >= 115 and SNo <=123;

[hic]

I assume that you have "records" that go over several lines; that the ID of the "record" is tagged with 115 and the following rows (up until 123) belong to the same "record". Then you can do something like

LOAD

  If(Trim(@1:4)='115', @5:n, Peek(RecordID)) as RecordID,

  If(Trim(@1:4)='120', @5:n) as Attribute120,

  If(Trim(@1:4)='121', @5:n) as Attribute121,

  If(Trim(@1:4)='122', @5:n) as Attribute122,

  If(Trim(@1:4)='123', @5:n) as Attribute123,

  @1:4 as Tag,

  @5:n as Text

FROM delivery.txt (fix, codepage is 1252);

HIC

[ali_hijazi]

use the table files wizard in QlikView and choose space as delimeter and qlikview will generate the following script

Load @1,      @2
FROM

C:\Users\ahijazi\Downloads\delivery.txt
(txt, codepage is 1252, no labels, delimiter is spaces, msq);

you can rename @1, and @2 in the wizard or here in the script

then you can add a where condition to the load statement where @1>=115 and @1 <=123

[#5]

Hello,

this works but I get a lot records with same ID but different content, see attachment.

I would like to have one unique record with all values.

Håkan

[hic]

Try the following

Temp:

LOAD

   If(Trim(@1:4)='115', @5:n, Peek(RecordID)) as RecordID,

   'Attribute' & Trim(@1:4) as Attribute,

   @4:n as AttributeValue

FROM delivery.txt (fix, codepage is 1252) Where Match(Trim(@1:4),115,120,121,122,123);

Data:

Generic Load RecordID, Attribute, AttributeValue Resident Temp;

Drop Table Temp;

See more on Generic Load.

HIC

[MarcoWedel · Accepted solution]

Hi Håkan,

another solution could be:

using this script:

table1:
LOAD If(Attr=100, Val, Peek(ID)) as ID, *;
LOAD [@1:3] as Attr, 
     [@4:n] as Val
FROM [http://community.qlik.com/servlet/JiveServlet/download/548030-111006/delivery.txt] (fix, codepage is 1252)
Where [@1:3]>99 and [@1:3]<124;

table2:
Generic
LOAD ID,
     'Attr'&Attr,
     Val
Resident table1;

DROP Table table1;

hope this helps

regards

Marco

[MarcoWedel]

same thought ...

regards

Marco