topic Re: Why does my load create null records ? in QlikView

Why does my load create null records ?

Wed, 25 Nov 2020 16:16:04 GMT

HI All,

I have an Excel file with 37,359 rows. However, when I do a simple straightforward LOAD of the file and store it in a QVD, it creates an additional 6 records with null values in the fields.

Is there a simple explanation to this problem.

Many thanks

Re: Why does my load create null records ?

PrashantSangle — Thu, 16 Mar 2017 10:43:56 GMT

Can you share your excel???

Regards,

Re: Why does my load create null records ?

Anil_Babu_Samineni — Thu, 16 Mar 2017 10:54:09 GMT

Strange, Can you Load your file like below

Sample:

Load * From Excel;

LET Var = NoOfRows('Sample');

Then create text object and use =$(Var) --- Then look how many rows you found in Qlikview

Re: Why does my load create null records ?

tresB — Thu, 16 Mar 2017 11:01:27 GMT

Simple possible explanation : there could be few invisible entries (like Alt+Enter) at the end of your excel.

Re: Why does my load create null records ?

ahaahaaha — Thu, 16 Mar 2017 11:01:40 GMT

Hi Maureen,

Can you try to delete the corresponding lines in Excel? If the empty six lines are loaded at the end of the data, then in Excel remove the six lines following the last data line. If empty lines appear in the middle of the load, in Excel sort the corresponding field and again delete six lines outside the data. This is like an option to combat the problem.

Regards,

Andrey

Re: Why does my load create null records ?

buzzy996 — Thu, 16 Mar 2017 11:05:10 GMT

there is one possibility u loaded ur data along with null records from ur source it self.

Means,for example i ur source if u have 10 rows with data and 6 rows with out data and that source is fixed,it will load all 16 records.

so what u could be do is,select only the data records and save a file and load again.

HTH.

Re: Why does my load create null records ?

rahulpawarb — Thu, 16 Mar 2017 11:05:21 GMT

Hello Maureen,

Trust that you are doing good!

As a workaround, open the excel file and delete the last empty 6 rows and save. Post that perform reload. This will extract 37,359 rows only.

Hope this will be helpful.

Regards!

Rahul

Re: Why does my load create null records ?

Thu, 16 Mar 2017 12:13:09 GMT

Thanks for your help Anil. Your script confirmed the number of rows I was loading in from my Excel sheet. It led me to thinking I had rows below my range of data with hidden control characters in them. And that was the case. Once I'd cleared the cells below the range QlikView only loaded in valid rows.

Many thanks

Re: Why does my load create null records ?

Thu, 16 Mar 2017 12:16:55 GMT

Apologies for not seeing your reply sooner. I'd resolved the issue by the time I returned to this board and seen your (and others) suggestion. Thank you all for confirming. MV