2 Replies Latest reply: May 26, 2010 12:28 PM by tonymacp RSS

    Error loading data

      Hi all,

      I'm getting some strange fails when I try to load data....

      First time, it works fine:

       


      Load *
      FROM myTB.qvd (qvd);
      Load Field1,
      Field2
      FROM myJanFile.qvd (qvd);
      CONCATENATE
      Load Field1,
      Field2
      FROM myFebFile.qvd (qvd);
      Load Account
      FROM Accounts.xls


      This worked fine and I got the following, correct record counts:

      myTB.qvd : 1,453,363 lines fetched

      myJanFile.qvd: 7,696,810 lines fetched

      myFebFile.qvd: 17,237,669 lines fetched

      Accounts.xls: 2,949 lines fetched

       

      But then I thought I'd like to do some month to month analysis, so I included a myMonth field. First time I calculated it using

       


      Month(DateField) as myMonth


      but when that didn't work I just entered it as text:

       


      'Jan' as myMonth


      on the January file, and 'Feb' on the February file.

      In both cases the load failed half way through myFebFile.qvd - first time I got t0 15,306,772 lines fetched and second time it was 14,672,567 lines fetched. Neither time did QV go on to Accounts.xls

      I'm hoping this isn't a memory issue as I have to add all the files for March, April and May!

      Any help greatly appreciated,

      Tony

       

        • Error loading data
          Steve Dark

          Hi Tony,

          If at all possible I would stamp the month on every row going into the QVD, rather than doing it on the way out. The reason for this is simply that if you do any manipulation of data when reading from a QVD (except renaming or dropping fields) it will switch from doing an Optimized load to a normal load. This will make your load from QVD literally 100 times slower.

          Another route is to build a calendar table using an autogenerate that has all the various ways that you can look at a date. This works fine as long as your data set is not too massive. I have uploaded a script I use to my files area. just change MyDate to DateField:

          http://community.qlik.com/members/stevedark/files/Attached+Files/calendar-script.txt.aspx

          Hopefully one of those solutions will work for you.

          My best guess on your original query is that it is a memory issue, based on the fact that the file is loading in non optimized load. QlikView has to load all the data uncompressed into memory before then compressing it down again. This could well cause issues. With that number of rows optimising the way data is stored is key. You could check my blog posting on performance for more information: http://bit.ly/bePwA7 .

          Hope that helps,

          Regards,
          Steve