Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Load Record
from delivery.txt
where SNo >= 115 and SNo <=123;
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
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
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
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
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
same thought ...
regards
Marco