topic Problems Loading Data in QlikView

Problems Loading Data

Mon, 29 Mar 2010 19:47:55 GMT

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.

Problems Loading Data

johnw — Mon, 29 Mar 2010 20:09:04 GMT

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.

Problems Loading Data

Mon, 29 Mar 2010 20:23:11 GMT

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.

Problems Loading Data

johnw — Mon, 29 Mar 2010 21:25:11 GMT

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.

Problems Loading Data

Mon, 29 Mar 2010 21:28:59 GMT

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

Problems Loading Data

johnw — Mon, 29 Mar 2010 21:49:30 GMT

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

Hopefully someone else has an idea.

Problems Loading Data

Anonymous — Tue, 30 Mar 2010 08:01:22 GMT

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.

Problems Loading Data

Tue, 30 Mar 2010 13:59:09 GMT

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!

Problems Loading Data

Anonymous — Tue, 30 Mar 2010 14:14:44 GMT

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

Problems Loading Data

Tue, 30 Mar 2010 14:25:52 GMT

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)

Problems Loading Data

Anonymous — Tue, 30 Mar 2010 14:31:42 GMT

I don't think the problem is the size ... i have many project with ascii table and milions of record

Problems Loading Data

Tue, 30 Mar 2010 14:40:26 GMT

I was mistaken, what you said about removing the msq partially worked.

I am loading 4 files, a 2007,2008,2009 and a YTD record

The YTD records I believe are now fully being loaded, the scripts are all identicle however with the three previous years, only 65,533 lines are being fetched from each.

Problems Loading Data

Anonymous — Tue, 30 Mar 2010 14:52:21 GMT

did u try with no qoutes?

FROM zzz.dat (ansi, txt, delimiter is '\t', no labels, no quotes);

Problems Loading Data

Tue, 30 Mar 2010 14:59:25 GMT

Just tried this, same results as before, only 65,533 lines fetched from three previous years, YTD fetched 146,554

Problems Loading Data

Tue, 30 Mar 2010 16:01:15 GMT

What alfasierra suggested worked in the end.

The three previous years files were corrupt, when I regenerated the files and removed the msq everthing loaded properly.

Thanks to everyone for there help!