Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
schneider2037
Creator
Creator

Ignore last row of Excel on LOAD

I have an Excel data source that comes with a merged cell in the last row that I want to ignore on LOAD


I feel there has to be an easy way to ignore this last row (which will be on a different row# every day) but can't seem to find the correct bits for "load everything from row 1 to last row minus 1."

Any help would be mightily appreciated!

1 Solution

Accepted Solutions
Anonymous
Not applicable

Hi,

you can try something like this:

Temp_Data:
LOAD

*,
rowno() as Recno
FROM
Excel.xlsx
(
ooxml, embedded labels, table is Sheet1);


Find the last record form source:
LoadMax:
Load max(Recno) as Recno1 Resident Temp_Data;

Let vMaxNo=Peek('Recno1',0,'LoadMax');

Data:
NoConcatenate
LOAD
*
Resident Temp_Data where Recno<>$(vMaxNo);

Drop tables Temp_Data,LoadMax;

View solution in original post

4 Replies
Anonymous
Not applicable

Hi,

you can try something like this:

Temp_Data:
LOAD

*,
rowno() as Recno
FROM
Excel.xlsx
(
ooxml, embedded labels, table is Sheet1);


Find the last record form source:
LoadMax:
Load max(Recno) as Recno1 Resident Temp_Data;

Let vMaxNo=Peek('Recno1',0,'LoadMax');

Data:
NoConcatenate
LOAD
*
Resident Temp_Data where Recno<>$(vMaxNo);

Drop tables Temp_Data,LoadMax;

marcus_sommer

Quite probably could this row be identified with some content of 'Total' in any column or that there is some content missing like a date which could be checked with isnum() or len() or maybe FIELD <> '' or something like that. This means something like:

load * from excel where FIELD1 <> 'TOTAL';

- Marcus

schneider2037
Creator
Creator
Author

Good thought, I'll give it a run!

schneider2037
Creator
Creator
Author

Gotcha.

Will take a run with it.


Thanks!