topic Excel Load - Limit Loaded lines for many Files in QlikView

Excel Load - Limit Loaded lines for many Files

Fri, 04 Nov 2011 16:08:23 GMT

I have a small Problem,

there are 30 Excel files each month

A) loading many excel files (same layout) - OK

B) limiting the number of lines loaded (to prevent garbage data) - OK

- Limiting the number of lines while loading many files - not OK

here is what i tried

EXCEL_TEMP:

Directory;

LOAD F1 as TIME,

     filename() as FILE,

     Main as Main1,

     Check as Check1,

     Main1 as Main2,

     Check1 as Check2,

     Main2 as Main3,

     Check2 as Check3,

     Main3 as Main4,

     Check3 as Check4,

     Main4 as Main5,

     Check4 as Check5,

     Main5 as Main6,

     Check5 as Check6,

     Main6 as Main7,

     Check6 as Check7,

     Main7 as Main_TOTL,

     Check as Check_TOTL,

     Correction

FROM

[.\*.10.2011.xlsx]

(ooxml, embedded labels, header is 6 lines, table is [TEMP]);

//where RowNo()<24;

it is reding the first file OK, but from the second file on, it only reads the first line of the following files.

any suggestions why ?

do i have to "reset" RowNo() somehow ?

Thanks

Andreas

Excel Load - Limit Loaded lines for many Files

swuehl — Fri, 04 Nov 2011 16:13:46 GMT

Hi Alex,

without trying myself, I would suggest using recno() function instead, because the help says:

RecNo ( )

Returns an integer for the number of the currently read row of the input table. The first record is number 1.

RowNo ( )

Returns an integer for the position of the current row in the resulting QlikView input table. In contrast to RecNo( ), which counts the records in the raw data table, the RowNo( ) function does not count records that are excluded by where clauses and is not reset when a raw data table is concatenated to another. The first row is number 1.

Regards,

Stefan

Excel Load - Limit Loaded lines for many Files

Fri, 09 Dec 2011 08:55:29 GMT

i forgot to say this, because i just rushed to the workstation and had to try it.

THANK YOU.

and a nice christmas holiday