Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: How to create records from this file structure?

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

7 Replies
Not applicable

Re: How to create records from this file structure?

Load Record

from delivery.txt

where SNo >= 115 and SNo <=123;

Employee
Employee

Re: How to create records from this file structure?

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
Honored Contributor

Re: How to create records from this file structure?

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

Not applicable

Re: Re: How to create records from this file structure?

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

Employee
Employee

Re: How to create records from this file structure?

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

Re: How to create records from this file structure?

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

Re: How to create records from this file structure?

same thought ...

regards

Marco

Community Browser