Solved: Why does my load create null records ? - Qlik Community

Report Inappropriate Content · ‎2017-03-16

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

MV

Anil_Babu_Samineni · ‎2017-03-16

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

View solution in original post

PrashantSangle · ‎2017-03-16

Can you share your excel???

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Anil_Babu_Samineni · ‎2017-03-16

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

tresesco · ‎2017-03-16

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

ahaahaaha · ‎2017-03-16

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

buzzy996 · ‎2017-03-16

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.

rahulpawarb · ‎2017-03-16

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

Report Inappropriate Content · ‎2017-03-16

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

MV

Report Inappropriate Content · ‎2017-03-16

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