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

Creating records from more lines

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

1 Solution

Accepted Solutions

Re: Creating records from more lines

Hi,

one solution could be:

QlikCommunity_Thread_199206_Pic1.JPG

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

8 Replies
pradeepreddy
Valued Contributor II

Re: Creating records from more lines

can u please elaborate your requirement..

Re: Creating records from more lines

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

Re: Creating records from more lines

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

- Marcus

Not applicable

Re: Creating records from more lines

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

Re: Creating records from more lines

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

Not applicable

Re: Creating records from more lines

Hello again,

qlikView-1.png

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

Re: Creating records from more lines

Hi,

one solution could be:

QlikCommunity_Thread_199206_Pic1.JPG

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

Not applicable

Re: Creating records from more lines

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

Community Browser