Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Rozgonyi
Partner - Contributor II
Partner - Contributor II

Qlik Sense - load Excel - number of null() rows are different

Hi together, 

I have an Excel File where i'm loading the data from.

When looking at my .xlsx File - between "Test" and "Datum" i have 3 empty rows.

When I try to implement it in QlikSense - between "Test" and "Datum" i only have 2 empty rows.

I have no merged cells or something like that. 

Does somebody now what could be the reason?

Screenshots with Excel and QlikSense preview attached.

 

Best Regards,

Richard

Labels (1)
3 Replies
Rozgonyi
Partner - Contributor II
Partner - Contributor II
Author

also the .xlsx File attached

mruehl
Partner - Specialist
Partner - Specialist

Hi Richard,

 

Weird behaviour... Same in Qlik Sense Feb 2022 and QlikView 12.5.

When I add 1 2 3 in the empty rows it works. Even when I delete the rows and recreate them all 3 rows are shown.

It looks like Row Number 4 is not shown. When I fill the first cell of this row it is shown.

When I delete Row 4 and recreate it, it is shown as well (all 3 rows).

When I save the xlsx to xls none or the 3 rows are shown.

 

Best Regards

Manuel

marcus_sommer

The reason is quite clear - the row isn't empty - at least not from the point of view from the Excel libraries which  parse the data.

For example, any data with white font on white background (sounds maybe silly but I think it's not so seldom) or anywhere (an unintended) space but more likely is any kind of invisible formatting and probably also an inclusion of this area within any expressions or names or similar stuff - anything which includes these area.

Further I'm wouldn't be surprised if such things aren't consistent between multiple releases and/or saving-states. OOXML allows respectively accepted various ways to save data within the xlsx. This means the tool and/or the libraries and their releases which create the file could have an impact.

As long as you have always the same kind of file you could it completely ignore and just using the appropriate header-lines to fetch the labels. If these rows may changeable it will be more difficult. Either you change the source appropriately or you need any kind of pre-load to count recno() / rowno() or similar to get the healer-lines information and/or loading the data without the labels and filtering and renaming the data/fields afterwards.

- Marcus