6 Replies Latest reply: May 30, 2012 9:53 AM by langaar9 RSS

    Script Execution Freezing

    Gerhard Laubscher

      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

        • Script Execution Freezing

          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

            • Script Execution Freezing

              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.

                • Script Execution Freezing
                  Gerhard Laubscher

                  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

              • Script Execution Freezing
                Gerhard Laubscher

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

                 

                Thank you both