Strange Load Problem

[gerhardl]

Hi,

This is the weirdest thing I have ever seen with QlikView.

I load data from fixed length text files into a module. All of a sudden after a reload this morning, all my fields were messed up (as if the source files changed) - please see the "wrong" image attached, you'll notice the "error description" and other fields make no sense.

Then when I just reload again (without making ANY changes to the load script or the source files or anything), everything is fine (see the "right" image attached.

I've reloaded about 10 times now and sometimes it's fine and sometimes not.

Has anyone seen something like this before? Am I crazy?

This is my load script, which I have not touched at all between loads:

LOAD

     if(left(@2:21,2)='09',right(@2:21,19),@2:21) as [Account no],

     @22:41 as [Policy No],

     @42:46 as [Policy Type],

     if(@47:47='B','Back-Dated', if(@47:47='M','Monthly','Error')) as [Premium Type],

     DATE(DATE#( @48:58, 'DD-MMM-YY')) as [Premium Date],

     round(@59:65/100,0.01) as [Premium Amount],

     if(@66:68='000','',@66:68) as [Error Code],

     if(@69:n='','Posted','Rejected') as [Result],

     if(@69:n='',null(),

     if(@69:n='More premiums found than the number of allowed premiums per policy','More premiums than allowed',

     if(@69:n='Account number does not exist','Invalid Account Number',

     if(@69:n='Account status code on account prevent charge of fee','Invalid Account Status',

     if(@69:n='Block code on account prevent charge of fee','Block on Account',

     if(@69:n='CD status on account prevent charge of fee','Invalid CD Status',

     if(@69:n='No policy found for account','No Policy Found for Account',

     if(@69:n='OTB - Insufficient credit available','OTB - Insufficient Credit Available',

     if(@69:n='Premium already charged for month','Premium Already Charged for Month',

     @69:n))))))))) as [Reject Reason],

     date(filetime()-1) as [File Date],

     @69:n as [Error Description]

FROM

(fix, codepage is 1252, header is 1 lines)

where @1:1 = 'D';

Gerhard

[#2]

Gerhard,

some time ago i had similiar problem. In my case it was some special characters in loaded data.

regards

Darek

[gerhardl]

I've checked and there are no special characters.

I currently load about 10 different files (1 per month) and the funny thing is the problem is with a different file each time. I load, then the December file is all messed up, then I do nothing except reload then the May and Feb files are all messed up and the December files is fine. This is crazy.

I run this exact same module on my laptop with no problems, but on this particular PC it causes these issues - the only differences between the computers are 32 versus 64 bit systems. But on the new computer I even built the module again from scratch (so it's not the exact same file) and the same problem.

I've never seen anything so nonsensical

[hic]

When I have had similar problems, it has always been a case of special characters or incomplete quotations (missing quotes). I would do the following to debug:

1. Verify that your files really are ANSI CP 1252 and not Unicode or UTF8.
2. Add some fields to the load:
   1. RecNo() as RecordNumber
   2. FileName() as FileName
      
3. Then find out on which record your data starts misbehaving. And take a look in the text file on this record.

Good Luck

HIC

[gerhardl]

Hi Henric,

The problem is that it isn't specific records or files giving me the issue.

Please see the 2 images attached. In the "May 2014 correct" image, in the top right I have May 2014 selected - this is one specific file (field is created based on file date). Looking at the charts and other objects, there are no problems with this file - all the Policy Types available for selection are valid policy types, and the error descriptions in the top left straight table are also all valid.

While I took that screenshot I reloaded the file (as you can see in image 1).

Right after the reload, while I still have May 2014 (same file, same records) selected, you can now see in the 2nd image that the records are now all wrong, showing error descriptions and policy types that are not correct based on the fixed length format of the file - it's as if the load script completely ignored the positions I told it each fields were in and just randomly decided how many characters go where.

I did nothing between these two images except reload the same data.... so based on that it cannot be specific records or a specific file with a problem. Does that make sense or am I being stupid?

[#6]

You may try to find exact field and exact row, which causes problem.

In my case it was something in dimension value.

regards

Darek

[hic]

This is the behavior you will get if you have strange characters. The Load script may interpret a strange character as carriage return (new line) and then you can get a number of subsequent errors caused by the first.

I still claim that you should try to identify on which row the first error occurs. E.g. by creating a table box with RecordNumber as first column (and sorted by this) and [Error Description] as second column. Then step your way through the records to find where the first error is.

HIC

[gerhardl]

I tried your proposal but because there are multiple files I did not use RecNo(), I rather used RowNo(), which creates a unique row number for each record across all files.

The moment I added this field to the load script everything was fixed - I've now reloaded a few times and all is well.

Thanks for the help.