14 Replies Latest reply: Mar 30, 2010 12:01 PM by Les Backman RSS

    Problems Loading Data

      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
          John Witherspoon

          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

              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
                  John Witherspoon

                  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

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

                        • Problems Loading Data
                          John Witherspoon

                          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
                              Johannes Sunden

                              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
                      Andrea Stecca

                      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

                        • Problems Loading Data

                          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
                          Andrea Stecca

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

                           

                            • Problems Loading Data

                              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
                              Andrea Stecca

                              did u try with no qoutes?

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