topic [XLS LOAD] limit number of lines being loaded in QlikView

[XLS LOAD] limit number of lines being loaded

Tue, 25 Oct 2011 09:11:36 GMT

good morning,

i have a LARGE excel file, with many sheets. usually, there is only one table per sheet.

but in this case, there are sheets with like 7 diferent tables (with different types of data)

qlikview allows to ignore a number of lines before it starts to load (header).

BUT, is there a way to STOP the load after a certain amount of lines ?

because, if it continues to load, it creates data-garbage after a few lines

(f.e. reading names, after a few lines there are numbers in the same column = mixed numbers and names)

any suggestions ?

thanks

erichshiino — Tue, 25 Oct 2011 09:33:44 GMT

Hi,

You can use a where clause:

LOAD @1,

@2,

@3,

@4,

@5,

@6,

@7,

@8,

FROM

[Test.xls]

(biff, no labels, table is sheet1$)

where RowNo()<1000;

You can replace the 1000 by the number of lines you want to load.

Regards,

Erich

Fri, 04 Nov 2011 11:46:04 GMT

where RowNo()<100; works

when there are f.e. 10 excel files, it reads all the necessary data from file1 OK,

BUT QV reads only the first line of data from the following files.

erichshiino — Fri, 11 Nov 2011 20:42:11 GMT

Hi, Alexander.

Please, try using recno() instead of rowno(), this way you can control the load on each input table.

Regards,

Erich

Tue, 15 Nov 2011 08:22:04 GMT

You are right, this was allready solved in: