Script Execution Freezing

[gerhardl]

Hi,

I have a QV File where I load from multiple .txt extracts.

I load an Account Extract.

Then I load multiple Daily Transaction Extracts lying in the same folder (where the Account Number exists in the Account Extract).

Then I load multiple Daily Application Extracts lying in the same folder.

etc.

Recently we added a new field in the application extract. I don't want to ask IT to regenarate all past extracts, but obviously when I try to pull in this new field, the script won't work because the field does not exist in most of the application extracts in teh folder.

So what I tried to do was put the NEW application extracts (with the additional field) in a separate folder. So I now do this in the script:

LOAD [Field 1],

[Field 2],

[Field 3]

FROM OLD FOLDER

LOAD [Field 1],

[Field 2],

[Field 3],

[BRAND NEW FIELD]

FROM NEW FOLDER

I don;t think this is teh rigth approac anyway, so ideas are more than welcome... but I thought it might work - unfortunately the entire script executes, but then it just stops at the end (see the image attached) - it loads everything but never finishes... it just freezes at this point (like in the image) and eventually I have to click abort and the entire QlikView application crashes.

Please help.... I need this new field and I have no idea how to use the different loads and joins and things. I've also attached my entire script if that helps.

Regards,

Gerhard

[#2 · Accepted solution]

Hi Gerhard,

My guess here is that the files from the new folder are being loaded into a seperate table to the files from the original folder (because of the new field, qlikview won't automatically concatenate the tables) so it will be left to do a nasty join on the common fields from teh 2 tables at the end of script. It will freeze and crash if the volumes of data in the tables are high.

If your intention is to have all the data loaded into one table, with the new field populated for the new files then use CONCATENATE before the load of the new files to force qlikview to add then onto the bottom of the existing table containg the older file data. Something like below....  Hope this helps - Matt

e.g.

Table1:

LOAD [Field 1],

[Field 2],

[Field 3]

FROM OLD FOLDER

CONCATENATE(Table1)

LOAD [Field 1],

[Field 2],

[Field 3],

[BRAND NEW FIELD]

FROM NEW FOLDER

[#3]

SuperQuinn's idea should work correctly.

How often do you load the historical files? Have you thought of creating a QVD that has all the historical data and then just appending the new file's data to the QVD? This would resolve your problem and most like cut down on load times.

[gerhardl]

Hi,

Thank you both - I will try the concatenate this morning.

I load this file every morning when I get to work - it takes almost 20 minutes. Can you please help me with the QVD? I have no idea how to go about it...

I have QV Personal Edition and for this file, I load the following every morning:

1. A NEW Full Account Extract, which replaces the previous account extract (about 400,000 records).

2. An ADDITIONAL Daily Transaction Extract with a KEY Account Number field. This I load from a folder where all historical transaction extracts are, so I load all of them including the new daily extract (usually about 50,000 records per day - up to 2 million records at the moment for them all).

3. An ADDITIONAL Daily Application Extract with a KEY ID Number field. This I load from a folder where all historical application extracts are, so I load all of them including the new daily extract (usually about 1,000 records per day - up to 400,000 records at the moment for them all).

4. An Insurance Policy Extract with the Account Number fields as Key again. Like the Account Extract I REPLACE this extract every day (as it is a full extract, not just new daily sign-ups) - about 25,000 records.

Your help is greatly appreciated. My script is attached to my first post.

Gerhard

[gerhardl]

Okay I figured out the QVD - wish I spent some time on it a year ago!

Thank you both

[#6]

Great stuff

I was about to say that there's a good section in the manual on incremental loads, covering qvds etc... if you've not already looked through this it's worth a read. Glad you got it working. - Matt

[#7]

Glad to have been some help. There is a lot that can be done with QVDs to save time, combine and interpret data. I use them in all of my solutions. Let me know if you have any other questions on their creation.