Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to create records from this file structure?

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

1 Solution

Accepted Solutions
MarcoWedel

Hi Håkan,

another solution could be:

QlikCommunity_Thread_121892_Pic1.JPG.jpg

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

View solution in original post

7 Replies
Not applicable
Author

Load Record

from delivery.txt

where SNo >= 115 and SNo <=123;

hic
Former Employee
Former Employee

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
Partner - Master II
Partner - Master II

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

I can walk on water when it freezes
Not applicable
Author

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
Former Employee
Former Employee

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

Hi Håkan,

another solution could be:

QlikCommunity_Thread_121892_Pic1.JPG.jpg

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