Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Excel Load - Limit Loaded lines for many Files

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

1 Solution

Accepted Solutions
swuehl
Not applicable

Excel Load - Limit Loaded lines for many Files

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

2 Replies
swuehl
Not applicable

Excel Load - Limit Loaded lines for many Files

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

Not applicable

Excel Load - Limit Loaded lines for many Files

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

THANK YOU.

and a nice christmas holiday