How to extract changes from the change store (Write table) and store them in a QVD - Vendor Reviews example

[Emile_Koslowski]

This article is currently under review.

This article explains how to extract changes from a Change Store and store them in a QVD by using a load script in Qlik Analytics. 

The article also includes

• An app example with an incremental load script that will store new changes in a QVD
• Configuration instructions for the examples

Scenario

This example will create an analytics app for Vendor Reviews. The idea is that you, as a company, are working with multiple vendors. Once a quarter, you want to review these vendors.

The example is simplified, but it can be extended with additional data for real-world examples or for other “review” use cases like employee reviews, budget reviews, and so on.

The data model

The app’s data model is a single table “Vendors” that contains a Vendor ID, Vendor Name, and City:

Vendors:
Load * inline [
"Vendor ID","Vendor Name","City"
1,Dunder Mifflin,Ghent
2,Nuka Cola,Leuven
3,Octan, Brussels
4,Kitchen Table International,Antwerp
];

The Write Table

The Write Table contains two data model fields: Vendor ID and Vendor Name. They are both configured as primary keys to demonstrate how this can work for composite keys.

The Write Table is then extended with three editable columns:

• Quarter (Single select)
• Action required? (Single select)
• Comment (Manual user input)

Prerequisites

• A shared space
• A managed space (optional but advised for the tutorial)
• A connection to the Change-stores API to the Analytics REST connector in the shared space. A step-by-step guide on creating this connection is available in Extracting write table changes with the REST connector in Qlik Cloud.

Steps

1. Upload the attached .QVF file to a shared space
2. Open the private sheet Vendor Reviews
3. Click the Reload App (A) button and make sure data appears (B) in the top table
   

   
   

4. Go to Edit sheet (A) mode
5. Drag a Write Table Chart (B) on the top table, and choose the option Convert to: Write Table (C).
   
   This transforms the table into a Write Table with two data model columns Vendor ID and Vendor Name.
   
   
   
6. Go to the Data section in the Write Table’s Properties menu and add an editable column
   
   
   
7. This prompts you to define a primary key inside the table. Click Define (A) in the table and use both Vendor ID and Vendor Name as primary keys (B).
   
   You can also just use Vendor ID, but we want to show that this also supports composite primary keys.
   
   
   
   
   
   
8. Configure the editable column:
   
   
   • Title: Quarter
   • Show content: Single selection
   • Add options for Q1Y26 through Q4Y26.
     
     Tip! Also add an empty option by clicking the Add button without specifying a value.
     
     
     
9. Add another Editable column with the below configuration
   
   
   • Title: Action required
   • Type: Single select
   • Options:  Yes and No
     
     

     

10. Add another Editable column with the below configuration
    
    
    • Title: Review
    • Type: Single select
    • Options:  Yes and No
      
      
      
      
11. The Write Table is now set up.
    
    Go to the Write Table’s properties and locate the Change store (A) section. Copy the Change store ID (B).
    
    
    
12. Leave the Edit sheet mode. Then add changes for at least two records. Save those changes.
    
    
    
13. Go to the app’s load script editor and uncomment the second script section by first selecting all lines in the script section (CTRL+A or CMD+A) (A) and then clicking the comment button (B) in the toolbar.
    
    
    
14. Configure the settings in the CONFIGURATION part at the end of the load script
    
    
    
15. Update the load script with the IDs of the editable columns.
    
    The easiest solution to get these IDs is to test your connection. Make sure the connection URL is configured to use the /changes/tabular-views endpoint and uses the correct change store ID.
    
    
    
16. Copy and paste the example load script (for the editable columns only) and paste it in the app’s load script SQL Select statement that starts on line 159:
    
    
    
17. Replace the corresponding * symbols in the LOAD statement that starts on line 176:
    
    
    
18. Choose which records you want to track in your table by configuring the Exists Key on line 216.
    
    This key will be used to filter the “granularity” on which we want to store changes in the QVD and data model, as the load script will only load unique existing keys (line 235).
    
    
    • $(vExistsKeyFormula) is a pipe-separated list of the primary keys.
    • In this example, Quarter is added as an additional part of the exists key to keep track of changes by Quarter.
    • Optionally, this can be extended with createdBy and updatedAt to extend the granularity to every change made:
      
      
      
19. Reload the app and verify that the correct change store table is created in your data model. The second table in the sheet should also successfully show vendors and their reviews.

Environment

• Qlik Cloud Analytics

[paulcalvet]

Hello @Emile_Koslowski 

Thank you, it's really useful.

How do you manage the step 15 & 16 when you publish your application ?

I try it and when I publish, the string  "New Safety stock rightsizing(69e8c1aef6708e83cb02bbb6)" changes, so the published application doesn't store the new value.

Thanks,

[Siciliani]

Hello @Emile_Koslowski. 

Agree with the comment above. When the change store is published, the keys for the editable fields change. 

If the api/analytics/change-stores could return the editable columns in the change store with the keys, that would be great.

[Sonja_Bauernfeind]

Hello @Siciliani 

This article is currently under review, and updates will come soon. If you want to be informed once changes are made to it, please subscribe to it.

All the best,
Sonja