1 Reply Latest reply: May 28, 2013 11:10 AM by Dave Hall RSS

    Loop, create and load without using reduce?

      I would like to use Server/Publisher to automatically produce a qvw document containing a subset of the total data. 

      Each qvw created, one for each customer, would contain only the data for that customer but I would like to do this without using loop and reduce.

       

      We have a system where many customers feed large amounts of data into one database.

      We need to automatically create separate copies of the same Qlikview qvs document, one for each customer which only conatins the data for that customer.

      Using loop and reduce is not an option because loading a single qvw containing all of the data would use too many resources. 

      Currently the server uses about 16GB of memory for about 15 or 20 minutes when loading the data for one customer and at different installations we have around 10, 20 or more customers each requiring their own copy of the qvw. 

      We cannot keep throwing more memory at the server to cope with all the customers. Setting up a server with over 240GB of memory so that we can do a loop and reduce is an expensive solution.  Doing the load one customer at a time on a server with around 24GB of memory is a much more cost effective option.

       

      Consequently I have two problems to solve.

      1. Creating the qvd's without having all the huge amounts of data in memory at once.

      2. Automatically creating the qvw documents for each customer without having to create a qvw document containing all the data and then doing a reduce.

       

       

      Ideally I would like to;

      Perform incremental loads a few times a day where I select just the changes from the database and then apply these changes to the existing qvd without having to load the whole existing qvd into memory first.

      When the qvd's have been updated then loop through the values in the customer table and automatically create a copy of the master qvw, one for each customer.

      On each copy of the qvw perform a reload having passed in a variable containing the customer id so that the load can know to only load the data for that customer from the qvd's.

       

      Is this possible?  or do I have to manually create a new qvw for each customer and schedule each one to load in a specific order?

        • Re: Loop, create and load without using reduce?

          Have managed configure a publisher task to do this.

          Firstly I split the reload task into two sections, and placed these two qvw documents in the \ProgramData\QlikTech\SourceDocuments folder.

          A. qvw with no UI components that performs the loads from the database and updates the qvd's

          B. qvw where the loads use the qvd's and a variable called vCustomer to do a select with a where clause to selectively load the data for one customer.

          E.G.

          FROM allData.qvd (qvd) where customer = 'CUST1';

           

          Then in publisher on the Documents -> Source Documents tab I setup two tasks

          1. A reload task for the reload document defined in A so that it loads the data from the database with a trigger to periodically load the data.

           

          2. A reload task on the main document with trigger "On Event From Another Task",  start on successful completion of the reload above (1).

          Also on the reload tab set reload to be on (ticked) and Script Parameter "vCustomer" and parameter value Customer_ID  (which is a document field that contains all the customer ID's but is not linked to any other tables)

          Also on the reduce tab I set reduced document name = %SourceDocumentName%_%ScriptParameter%

          None of the other fields on the reduce tab have any values defined other than the default.

          On the Distribute tab the the Manually -> Distribute to folder was set to where the user visible qvw documents are placed.

           

          This creates a qvw and performs a reload for each customer then copies the qvw to the distibute to folder with a name specific to each customer.

           

          The qvw size never gets any bigger than that required for the data for one customer meaning I do not have to size the server with enough memory to create a qvw containing the data for all customers.

          N.B. You have to be careful about the amount of memory used during the database load and the creation of the qvd's