topic Re: How to create records from this file structure? in QlikView

How to create records from this file structure?

Fri, 13 Jun 2014 07:45:05 GMT

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

Re: How to create records from this file structure?

Fri, 13 Jun 2014 07:57:04 GMT

Load Record

from delivery.txt

where SNo >= 115 and SNo <=123;

Re: How to create records from this file structure?

hic — Fri, 13 Jun 2014 08:00:40 GMT

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

Re: How to create records from this file structure?

ali_hijazi — Fri, 13 Jun 2014 08:01:14 GMT

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

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

Fri, 13 Jun 2014 13:02:58 GMT

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

Re: How to create records from this file structure?

hic — Fri, 13 Jun 2014 13:39:02 GMT

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?

MarcoWedel — Fri, 13 Jun 2014 13:46:22 GMT

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

Re: How to create records from this file structure?

MarcoWedel — Fri, 13 Jun 2014 14:05:02 GMT

same thought ...

regards

Marco