Creating records from more lines

[#1]

I have a file with record brake on line 100. How do I load a file like this to get a new record on each line 100?

100 6454600156

115 6454600156002

120 20160118

121 SV645460015

123 N

115 6454600156003

120 20160118

121 SV645460015

123 N

100 6564511195

115 6564511195011

120 20160118

121 SV656451119

123 N

115 6564511195012

120 20160118

121 SV656451119

123 N

kindly

Håkan

[PradeepReddy]

can u please elaborate your requirement..

[Peter_Cammaert]

You want to store an ID=100 line and all subsequent lines until the next ID=100 line into a single row? Difficult to accomplish if you cannot be sure how many extra lines will follow. If there will always be 8 additional lines, then it's doable.

Please explain the situation a bit more. Thanks.

Best,

Peter

[marcus_sommer]

I think this will be helpful: Peek() or Previous() ?

- Marcus

[#5]

Hello, the number of lines can differ, sometimes there is one or two lines more.

In other programming languages you can create a new record every time you get

a new 100.

id=6454600156

115_1= 6454600156002

120_1= 20160118

121_1= SV645460015

123_1= N

115_2= 6454600156003

120_2= 20160118

121_2= SV645460015

123_2= N

Would it be easier if I use 115 as ID and split it into two records?

kindly

Håkan

[Peter_Cammaert]

Almost everything can be done in QlikView script (as long as the data allows it). However, it serves no purpose to propose a solution that doesn't match your expected output.

Can you tell us what table data you would like to obtain from the example data in your original post? Best would be to specify column names and what they should contain. Thanks.

Peter

[#7]

Hello again,

This is the table I would like to have from this infile

100 6454600156

115 6454600156002

120 20160118

121 SV645460015

123 N

115 6454600156003

120 20160118

121 SV645460015

123 N

100 6944600176

115 6944600176005

121 SV694460017

120 20160111

122 ELO: Diameter 75  (77mm)

123 Y

115 6944600176006

121 SV694460017

120 20160111

122 ELO: Diameter 75 (77mm)

123 Y

As you see doesn´t line 122 exist in every record.

kindly

Håkan

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

table1:
LOAD RecNo,
    Replace(FieldName&'_'&AutoNumber(RowNo(),RecNo&'/'&FieldName),'100_1','ID') as FieldName,
    FieldValue;
LOAD RangeSum(Peek(RecNo),-(FieldName=100)) as RecNo,
    *;
LOAD SubField(line,' ',1) as FieldName,
    Mid(line,Index(line,' ')+1) as FieldValue
Inline [
    line
    100 6454600156
    115 6454600156002
    120 20160118
    121 SV645460015
    123 N
    115 6454600156003
    120 20160118
    121 SV645460015
    123 N
    100 6944600176
    115 6944600176005
    121 SV694460017
    120 20160111
    122 ELO: Diameter 75  (77mm)
    123 Y
    115 6944600176006
    121 SV694460017
    120 20160111
    122 ELO: Diameter 75 (77mm)
    123 Y
];

hope this helps

regards

Marco

[#9]

Hello,

thanks for all help. One question, can I sort the table on any FieldName, my intention

is to sort it on 123_1  ?

kindly

Håkan