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

[#1]

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

[Gysbert_Wassenaar · Accepted solution]

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);

[#3]

Thank you Gijsbert!!

Your answer was very helpful and work out pretty well.

[rogeriogyn]

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