Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to load a number of rows within a specific range.

Hi everyone,
I got the following situation:
My datasource is an Excel file which contains more than 7500 rows. However, the application which I need the data for only supports a max load of 2500 number of rows at a time. Therefore i would like it to load the data in multiple stages. 
I was wondering whether it is possible to load the data in 3-4 stages by defining the range of row numbers in the script. For example; the first 2500 rows:
[Table X]:
LOAD
First 2500 Recno() as ID,
     Employee_number,
     Company_name,
     Organization_number,
     Postal_code
From
[analysis.xlsx]
Then I want  Qlikview to resume by starting loading from Row number 2501 to 5000 and so on.
I can also achieve this by editing the source data. However, i rather dont want to make any modifications in the original datafiles.
Your help is much appreciated!
Stefan

Tags (4)
1 Solution

Accepted Solutions

Re: How to load a number of rows within a specific range.

You can set the header to 2500 lines and then load the first 2500 records. Something like:

[Table X]:

First 2500

Load

@1 as Employee_number

@2 as Company_name

@3 as Organization_number

@4 as Postal_code

from [analysis.xlsx]

(ooxml, no labels, header is 2500 lines, table is Sheet1);


talk is cheap, supply exceeds demand
3 Replies

Re: How to load a number of rows within a specific range.

You can set the header to 2500 lines and then load the first 2500 records. Something like:

[Table X]:

First 2500

Load

@1 as Employee_number

@2 as Company_name

@3 as Organization_number

@4 as Postal_code

from [analysis.xlsx]

(ooxml, no labels, header is 2500 lines, table is Sheet1);


talk is cheap, supply exceeds demand
Not applicable

Re: How to load a number of rows within a specific range.

Thank you Gijsbert!!

Your answer was very helpful and work out pretty well.

rogeriogyn
New Contributor III

Re: How to load a number of rows within a specific range.

I needed the same, so using the Helpfy Answer I created this.

I work for me, since I'm using really big data, and we had some memory issues. Now it's fine.

let _step = 3;

let _cont = 0;

let _rows = 30;

let _carq = 0;

let _header = 0;

for _cont=0 to $(_rows) step $(_step)

let _header = $(_step)*$(_carq);

let _carq = $(_carq) + 1;

TabX:

LOAD @1 as Cod,

     @2 as Desc

FROM

          TesteNrLinhas.txt (txt, codepage is 1252, no labels, delimiter is '\t', msq, Header is $(_header) lines)

WHERE

          RowNo() < $(_step)

          ;

Store TabX into TabX$(_carq).txt (txt);

drop table TabX;

next

Community Browser