Error loading data

[#1]

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

[stevedark · Accepted solution]

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

[#3]

Thanks Steve, I thought I might end up doing it on the way into the QVD. Just doing it now.....

Also, I was completely out of space on my server, so I don't suppose that was helping much.

cheers,

Tony