Problems Loading Data

[#1]

I am loading 3 years of data all from ascii files organized by invoice number. There are no errors in loading however I noticed some line items that are in the ascii files are not in Qlikview. Has anyone else experienced this or have a possible solution to this?.

When I do a search, there are no selections made that would limit the data output.

[johnw]

While I haven't had this problem personally, I've heard of it. I believe the problem in that case was that there was some sort of end of file character in the file, so no records past that point were being read in. I can't remember the solution. It's possible you have the same problem.

[#3]

It seems to be a chunk of data records in the middle of the file that do not load.

UPDATE: I tried moving one of the missing records to the top of the table and it appeared in Qlikview. Though this fixed this one record, there is no way to know how many are like this.

[johnw]

That seems to support the theory that there's some sort of bad character(s) in the file. After all, if moving the row itself to the top makes it appear, the problem likely doesn't have anything to do with the row itself.

If you can figure out exactly which row is the last one being read in, you might be able to look at the file with a hex editor and see if there's one or more messed up characters at that point. That's all I'm thinking of, anyway. If you find something, you could remove it and see if the load works. If it does, you'd need to figure out how the ascii file got corrupted in the first place.

[#5]

I will try that thanks, but from what I have seen, the rows missing are not all together in the file

[johnw]

Hmmm, yeah, that would indicate that my guess is wrong. And I don't have another guess. Sorry!

Hopefully someone else has an idea.

[Anonymous]

I'm guessing here, but could it be that you are looking for your records using an invoce number or something similar?

The thing is.. since numerical interpretation is at least hundreds of times faster than string interpretation, QlikView tries to interpret things numerically for performance reasons. If you have a field with the following values [ 005, 05, 5, 03 ] and load these into QV, QV will interpret the first three values as 5 but represent them as 005 which was the presentation value of the first number 5 value. You will see two values in a listbox: [005, 03] and the frequency on 005 will be 3. QlikView only stores each distinct value once and then just refers to that value when loading additional number 5 values. In this case one can load the values using the Text() function if they're in fact no supposed to be numerical. This way you will have a distinction between the values and get the full list in a listbox [005, 05, 5, 03].

Without having looked at your document or ascii files I'm suspecting that this might be your problem and that the records might actually be loaded already without you being able to find them.

[#8]

I am looking for records via an invoice number however each one is distinct with 8 digits. None starting with 0.

I will try what you suggest but don't expect it to solve the problem here, thanks for the idea though!

[Anonymous · Accepted solution]

hi

did u tried to remove msq from the load?

instead of this

LOAD @1,
@2,
@3,
@4
FROM MVTABANNI.DAT (ansi, txt, delimiter is '\t', no labels, msq);

load without ,msq

LOAD @1,
@2,
@3,
@4
FROM MVTABANNI.DAT (ansi, txt, delimiter is '\t', no labels);

ciao

[#10]

I just tried this, when loading the script i get Unknown file format specifier:

UPDATE i forgot to take away a comma, no more error but did not fix the problem.

I am starting to think it is related to the size of the ascii as there are more then 65K records (the limit in excel)