topic Record Count Mismatch in QlikView

Record Count Mismatch

shannoypaul — Fri, 08 Aug 2014 12:42:47 GMT

Hi,

I am reading data from an excel file, using cross tab function. The new table now consists of 780 records.
I am making a QVD for this new table and again reading it from the QVD, now it reads only 775 records.

I thought there was some issue with my script, but I checked using a two table boxes one having the excel read contents and the other containing QVD read contents. Both of the table boxes had 775 records. Further i checked data in excel, it is completely perfect.

Then why was it showing 780 records being record, it still shows the same but no issues with the data.

Please let me know if I have missed out anything.

Thanks in advance!

Re: Record Count Mismatch

Fri, 08 Aug 2014 12:45:24 GMT

Any chance there are duplicate records in your Excel file? QlikView will only hold the unique records across all fields.

Re: Record Count Mismatch

marcus_sommer — Fri, 08 Aug 2014 13:29:56 GMT

You could use rowno() or recno() to make each row definitely unique. Further you could look at the table-viewer (ctrl+t) and/or create a structure-table (right click in sheet --> new objects --> structure-table) to investigate your data-structure.

- Marcus

Re: Record Count Mismatch

Fri, 08 Aug 2014 13:43:26 GMT

Post your script. do you write some distinct in the script?

Marco

Re: Record Count Mismatch

Fri, 08 Aug 2014 13:52:47 GMT

It could be an issue with the source excel file. I had the same issue recently when trying to upload the data. After trial and error I managed to tally the records between the excel file and the table.

Open your excel file, go to the last record in the sheet, select all the rows below the last record (technically all empty rows) and delete it. Re-run the script and see if the count matches.

This worked for me but honestly I am not sure why it causes such a problem in the first place.

Hope this helps.

Re: Record Count Mismatch

Fri, 08 Aug 2014 13:59:34 GMT

Below are the possible cases:

1. If you using Cross function for loading, please check is there any null values exist on the Data field values. Cross table wont load the Null values. Use Isnull condition to convert null value in the zero.

2. In Table Boxes, QLikview will show unique values only. Use Row No or Rec No function to create the unique key on the the each row.