topic Re: Creating records from more lines in QlikView

Creating records from more lines

Tue, 12 Jan 2016 11:22:43 GMT

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

Re: Creating records from more lines

PradeepReddy — Tue, 12 Jan 2016 11:41:13 GMT

can u please elaborate your requirement..

Re: Creating records from more lines

Peter_Cammaert — Tue, 12 Jan 2016 11:45:46 GMT

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

marcus_sommer — Tue, 12 Jan 2016 11:46:54 GMT

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

- Marcus

Re: Creating records from more lines

Tue, 12 Jan 2016 12:24:43 GMT

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

Peter_Cammaert — Tue, 12 Jan 2016 13:27:57 GMT

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

Re: Creating records from more lines

Tue, 12 Jan 2016 14:23:13 GMT

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

Re: Creating records from more lines

MarcoWedel — Wed, 13 Jan 2016 00:25:23 GMT

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

Re: Creating records from more lines

Wed, 13 Jan 2016 07:58:08 GMT

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