Skip to main content

Step-by-step - Incrementing data loads using the Store command

This step-by-step example shows how to increment data loads using the Store command in Qlik Sense SaaS.

You can use Store to create a QVD file and load that file into DataFiles. This data can be, for example, data records that are only available from a database for a finite amount of time, such as a certain number days prior to the current date. The historical data can be loaded from a QVD file that you create, and then combined with the latest available data from a source database. The latest data can then be inserted back into the QVD file. This example demonstrates this use case.

You can also use Store to store data from a connection that you only want store once, and then add new data from a database. This can reduce subsequent data load times, as well as reduce the load on database servers, your network, and so on. This example does not consider this use case. However, the same steps would generally apply.

In this example, we create a connection using the Standard Search Twitter API and search for a specific text term. The search matches against tweets for the past 7 days, the limit imposed by Twitter. In this example, then, the Standard Search Twitter API is limiting the amount of data that is loaded from the Twitter database.

Using Store, we then store the Twitter data table into a QVD file and load that file into DataFiles. We then make requests for the newest data from Twitter, and load the data into our app along with the data from the QVD file. That data is loaded back into QVD file, thereby incrementing the QVD file.

We then publish the app, and then reload our published app with the latest data.

For more information about the tasks and concepts in this example, see:

Prerequisites

  • Cloud hub (Qlik Sense SaaS) subscription.
  • Twitter account to use the Twitter API.
  • You should know how to create an app in a cloud hub. For more information, see Creating apps and adding data.
  • You should know how to use the data load editor. For more information, see Using the data load editor.

Tasks

The following tasks are required to complete this example:

  1. Create an app and connect to Twitter
  2. Select data from Twitter
  3. Add script statements to create and increment QVD file from Twitter data
  4. Run script
  5. Set the data reload schedule

Create an app and connect to Twitter

  1. Create an app in your cloud hub workspace.
  2. Open the script editor. The option to open the script editor is shown when you create a new app. You can also access the script editor from the data load editor.
  3. Under Data connections in the right menu, click Create new connection. The Create new connection window opens.
  4. Select Twitter from the Data sources drop-down list. An authentication window opens.
  5. Click Authenticate. A Twitter API authentication windows opens. You may need to enable pop-ups in your browser.
  6. To authorize Qlik web connectors to use your Twitter account, enter your Twitter credentials.
  7. Click Sign in. A Twitter window opens, displaying an access code. Copy the code.
  8. In the Qlik Sense authentication window, paste the code, and then click Verify. The connection is authenticated.
  9. Click Test Connection to confirm the connection has been created.
  10. Name the connection, and then click Create. The connection is created, and the data load editor opens. The connection is added to your Data connections list in the right menu.

Select data from Twitter

After you have created your connection, you can select the data that you want to load from Twitter.

  1. Add a new tab to your script in the script editor.
  2. Name the tab. We called our tab New Data (Twitter). Place your cursor in the script editor window.
  3. In the data load editor, click Select data for your Twitter connection.

    The Select data to load window opens.

  4. Select the table called Search, and then select a term to search. For our example, we are using "Kubernetes".

     

    Data load window with Search table selected

    Data load window with Search table selected.

  5. Click Preview data. The data fields for the Search table are shown. Select the fields that you want to load. For our example, we select the following fields: id, created_at, text, source, and user_name.

     

    Data load window showing a preview of data

    Data load window showing a preview of data.

  6. Click Insert script. The script segment is added to the New data (Twitter) tab in the script editor in the data load editor.

  7. Name the table by adding a line above the Load statement. In our example, we call it TwitterSearch

     

    Your tab should look similar to the following screenshot (not including the comments):

     

    New data (Twitter) tab in the data load editor

    New data (Twitter) tab in the data load editor.

  8. To verify that your data load is working, click Load data in the top menu. The data is loaded.

     

    Data load progress window

    Data load progress window.

If you want, you can view data in the tables and fields in the data model viewer. You also create a table in your app to view your current data.

Add script statements to create and increment QVD file from Twitter data

After you have set up your app to load data from Twitter, you can store that data in a QVD file. As the Twitter Standard API only allows you to pull data from the previous 7 days, our QVD will initially contain that data after the data is loaded.

  1. Add a new tab to your script in the script editor.
  2. Name the tab. We called our tab Historical data (QVD).
  3. Add the following script statements in the tab. The comments (//) are for reference. These will not be executed when you run the script.

     

    Information noteIf you are copying and pasting from Firefox, you may need to first paste into another document, such as Word, for the line breaks to appear properly in the script editor.

     

    //Method to test QVD file size. If the file does not exist returns <null> let size = FileSize('lib://DataFiles/AllTweets.qvd'); //Set the historical data pull range to a variable LET vRollingDate = Today()-90; //If the file exists and contains some data then the if statement is executed; //if the file does not exist, i.e. is null, then the if statement will be skipped if not isnull(size) then TwitterSearch: LOAD * FROM ['lib://DataFiles/AllTweets.qvd'](qvd) //Twitter ids are unique; ensures only new tweets added WHERE NOT EXISTS (Search.id) //Load last 90 days of data from the qvd file; //QVD file provides historical data beyond Twitter Standard API limit of 7 days. //Also ensures that stored incremented qvd file does not grow too large in size. AND FLOOR (Date([Search.created_at]))>=$(vRollingDate); end if

     

    Your tab should look similar to the following screenshot:

     

    Historical data (QVD) tab in the data load editor

    Historical Data (QVD) in the data load editor.

  4. Add another new tab to your script.
  5. Name the tab. We called our tab Store to QVD.
  6. Add the following script statements in the tab.

     

    //Regardless of the QVD file existing previously or not, updated data gets stored into the QVD. STORE TwitterSearch INTO [lib://DataFiles/AllTweets.qvd](qvd);

     

    Your tab should look similar to the following screenshot:

     

    Store to QVD tab in the data load editor

    Store to QVD tab in the data load editor.

Run script

When you run the script for the first time, the data is loaded from Twitter, and the QVD file named AllTweets.qvd is created from the Twitter data. The QVD file is added to your DataFiles space. When you run the script again the data is loaded from Twitter and the QVD file. The QVD file is then updated with the data that you loaded from the QVD file.

  1. Click Load data. The first time that you run the script is run the data is loaded from Twitter, and the AllTweets QVD file is created using the data. The QVD file is added to your DataFiles space, and can be viewed by clicking Select data.

     

    The first time that you run the script, you should see a message similar to the following:

     

    Data load progress window

    Data load progress window.

     

    The second and subsequent times that you load data, you should see a message similar to the following:

     

    Data load progress window

    Data load progress window.

  2. Create charts and tables in your app using your data.

Set the data reload schedule

After you have loaded your data, you can show that data in your app. One way to reload the data, as we have seen, is to open the script editor and click Load data. However, you can also reload data manually, or on a schedule that you choose, in the cloud hub user interface. For more information, see Reloading app data in the cloud hub.