How to extract changes from the change store (Write Table) and store them in an Excel file using Qlik Automate

[Emile_Koslowski]

This article explains how to extract changes from a Change Store by using the Qlik Cloud Services connector in Qlik Automate and how to sync them to an Excel file.

While the example uses a Microsoft Excel file, it can easily be modified to create a CSV as well.

The article also includes:

• An automation example you can download and import (see Qlik Automate: How to import and export automations): Automation Example To Extract Change Store Data and Store in Microsoft Excel.json
• A Qlik app example with an inline load script with example data: Write Table Purchase Order Demo.qvf
• Example purchase order template Excel file: Purchase order template.xlsx
• Configuration instructions for the example

Content

• Prerequisites
• Installing the example app
• Create the automation and get the SharePoint metadata
• Configure the automation
• Running the automation from the sheet
• Bonus: overwriting an existing Excel file
• Bonus: Sending the Excel file as an email attachment

Prerequisites

You will need the following:

1. A working Write Table with a set of editable columns and some example values already stored in it.
   
   For more information on the Write Table chart, see Write Table | help.qlik.com.
   
   Here is an example of the Write Table that will be used in this article. It has the following configuration:
   
   
   
   

   • Week start is included in the primary key because the purchasing process (making the changes) happens on a weekly basis.

   • Product Name is included in the primary key to make sure it is always returned when retrieving changes through the Get Current Changes From Change Store block in Qlik Automate.
     
     Below is an example of the table in an app:
     
     

2. An Excel file that will be the template for purchase orders.
   
   
   • This must be stored in SharePoint or OneDrive.
     
     
   • This file must contain a sheet with an empty table on the sheet. Take note of the sheet name/id and the table name and id.
     
     An example template file is attached to this article.
     
     
     
     
3. A destination folder in Microsoft SharePoint or OneDrive where the new purchase order files should be created.
   
   Example of the SharePoint structure:
   
   

Installing the example app

Optionally, you can use the app that is included in this article. Follow these steps to install the app and configure the Write Table:

1. Import the app into your Qlik Cloud tenant.
2. Go to the Data Load Editor and do a manual reload.
3. Open the Inventory management sheet and go to edit mode.
4. Drag a Write Table Chart (A) on top of the Straight Table.
   
   
   
   
5. Select Convert to: Write Table (B).
   
   

   

6. Go to the Change store section in the Write Table's configuration panel and define a new primary key.
   
   
   
7. Select Product ID, Product Name, and Order Date as columns for the primary key, then click Save.
   

   
   

8. Add the following editable columns to the Write Table:
   
   
   1. To purchase: Manual user input
      
      
      
      
   2. Priority: Single selection: High, Low
      
      Tip! Add an empty option by clicking the + button without providing a value.
      
      
      
      
   3. Note: Manual user input
      
      
      
      
9. Leave the edit sheet mode and take note of the change store id for the change store that is linked to the Write Table.
   
   
   
   
10. First, make a selection in the app, then provide some example changes in the Write Table to use as example data to configure the automation:
    
    
    

Create the automation and get the SharePoint metadata

1. Create a new automation. See Qlik Automate for details.
2. Before the automation can be configured, SharePoint metadata is required that cannot be retrieved dynamically in the automation.
3. Get the SharePoint Drive Id. You need access to the Drive in the SharePoint Site on which the Excel template file is stored. To do this, add the List Drives From Site block from the Microsoft SharePoint connector. Connect your SharePoint account to this block.
4. Click the input field for the Site Id in the Inputs tab (A) on the List Drives From Site block and use the do lookup functionality (B) to search for the site (C).
   
   
   
   
5. Right-click the List Drives From Site block (A) to perform a Test Run (B) of the automation.
   
   Once completed, review the automation’s run history to retrieve the correct Drive Id from the List Drives From Site block to find the Drive Id of the drive you want to use.
   
   Store this id for later use.
   
   
   
   
   
   

   

6. Add a List Items On Drive block from the Microsoft SharePoint connector to the automation.
   
   This block will be used to retrieve the folder id for the destination folder in which the purchase orders should be created. Configure the block with the Drive Id from step 5.
   
   Specify root as the Item Id.
   
   
   
   
7. Run the automation manually to review the automation run’s history and retrieve the correct Item Id for your destination folder from the output of the List Items On Drive block.
   
   
   
   Tip! If your destination folder is nested in other folders, you will need to repeat this step until you have the item Id of the destination folder. Start with root as the Item Id and then replace it with each folder’s item id as you go towards the destination folder.
   
   
8. Right-click both SharePoint blocks and collapse (A) their loops.
   
   Then right-click them again to disable (B) them as they are no longer needed for the regular automation runs.
   
   But you might want to keep them inside the automation if you want to take this automation to a different SharePoint team or folder structure in the future. If you are certain you will not need another drive id or folder id for this automation, you can delete the blocks.
   
   

   
   
   

Configure the automation

1. Add an Inputs block and configure it with one required parameter weekStart. 
   
   This will be used to capture the weekStart date from the app when a user triggers the automation from the app.

   
   

2. Add six Variable blocks to the automation and add the following variables of type String:
   
   
   • driveId: store the Drive Id from step 5 in the previous section
   • folderId: store the folder’s item id of step 7 in the previous section
   • templateFileName: name of the Excel file template
   • destinationFileName: name of the purchase order file to create
   • sheetName: name of the sheet that contains the table
   • tableName: name of the table
     
     
3. Set values for the variables that correspond with your Excel file template. If you are using the example template from this article, you can supply the following values:
   
   
   • driveId: store the Drive Id from step 5 in the previous section
   • folderId: store the folder’s item id of step 7 in the previous section
   • templateFileName: Purchase order template.xlsx
   • destinationFileName: Purchase order_{$.inputs.weekStart}.xlsx
     
     Tip! if your date format uses slashes, it will not work for the Excel file name, as Excel will create directories. Instead, use a different date format, such as MM-DD-YYYY or MM_DD_YYYY.
     
     
     
     
   • sheetName: Purchase order
   • tableName: Products
     
     

4. Add an Open File block from the Cloud Storage connector and configure it as follows:
   
   
   • Connector: Microsoft SharePoint
   • Path: templateFileName variable
   • Drive Id: driveId variable.
     
     
     
5. Add a Copy File block from the Cloud Storage connector.
   
   This block will copy the template Excel File and create a new empty Excel file to create the purchase order.
   
   Configure the block as follows:
   
   
   • Source File: select the “Open File on Microsoft SharePoint …” block
   • Destination connector: Microsoft SharePoint
   • Destination Path: folder path + / + destinationFileName variable
   • Drive Id: driveId variable
     
     
     
6. Add a List Items On Drive block from the Microsoft SharePoint connector.
   
   This block will be used to get the Item Id for the file created by the Copy File block.
   
   Configure it as follows:
   
   
   • Drive Id: driveId variable
   • Item Id: folderId variable
     
     Tip! Right-click on the List Items On Drive block and choose Collapse loop. This saves space in the automation and makes it more readable.
     
     
     
     
7. Perform a manual run of the automation to verify that the destination Excel file is created and is returned by the List Items On Drive block.
   
   
8. Add a Lookup Item In List block from the Lists blocks section.
   
   This block will be used to retrieve the item id of the file created by the Copy File block (because this id is not returned by the Copy File block).
   
   Configure the block as follows:
   
   
   • List: full output from List Items On Drive 2 (A)
   • Conditions:
     
     
     1. Click the first input box (Property from) in the Condition parameter (B) and select the name parameter (C).
        
        
        
        
     2. Set the next input field (operator) to equals (D).
        
        
        

     3. Set the third one (Value) to the destinationFileName (E) variable.
        
        

9. Add a Create Workbook Session block from the Microsoft Excel connector and configure it with the output from the Lookup Item In List block.
   
   
   
   
10. Add a List Current Changes From Change Store block from the Qlik Cloud Services connector.
    
    This block will return all saved changes from the change store.
    
    Configure the Store Id parameter with the write table’s change store id.
    
    

    

11. Add a Filter List block. This block will be used to filter orders for the correct week.
    
    Configure it as follows:
    
    
    • List: output of the List Current Changes From Change Store block
    • Conditions:
      
      
      • Property from: the Order Date (A) returned by the List Current Changes From Change Store block.
        
        Tip! Make sure this field is part of your primary key so it is included in the change data from the change store.
        
        
        

      • Operator: equals

      • Value: the weekStart input from the Inputs block
        
        

        

12. Add an Update Rows In Worksheet block from the Microsoft Excel connector.
    
    This block will only be used to update a single cell in the Excel template with the date of the Week Start.
    
    If your Order form does not have such a value, you can ignore this step.
    
    If it has multiple of these values, then you can repeat it for each of them.
    
    Configure the block as follows:
    
    
    • Drive Id: Drive Id from the “Parent Reference” from the Lookup Item In List block
    • Item Id: Id from the Lookup Item In List block
    • Worksheet Name: Sheet Name variable
    • Start Cell: Coordinate of the Excel cell that needs to be updated. In the example template, this is G2.
    • End Cell: Same coordinate as the Start Cell since we are only updating a single cell.
    • Values: The weekStart input from the Inputs block. Since it will only update a single cell, there is no need to create an array as described in the help text.
      
      

      

13. Add a Loop Batch block.
    
    This block will divide the output from the Filter List block across multiple batches that can be added to Excel batch by batch.
    
    Configure the block as follows:
    
    
    • Loop over items of list: Filter List block
    • Amount of items per batch: 100
      
      

14. Add a Variable block inside the Batch Loop block.
    
    Create a new variable RowsString of type string.
    
    This variable will be used to build a string containing the changes in a format that is accepted by the Microsoft Excel connector.
    
    Add the following operations in the variable block:
    
    
    • Empty RowsString: this makes sure that on every new batch, the variable is emptied.
    • Append to RowsString: this will add the first text value to the string. Configure this to a single square bracket [.
      
      

      

15. Add a Loop block.
    
    This block will iterate over all the changes in the current batch.
    
    Configure it as follows:
    
    Loop over items of list: Loop Batch > Batch
    
    

    

    Tip! Perform a manual run of the automation to make sure there are example values in the Loop block.
    
    
16. Add a Get User block from the Qlik Cloud Services connector inside the loop of the Loop block.
    
    This block will be used to retrieve the user information for the updatedBy parameter in each change.
    
    Configure it to use the updatedBy parameter as input for the User Id parameter.
    
    
    
    
17. Add a Variable block to the Get User block and configure it to use the RowsString variable.
    
    Add an Append to RowsString operation and configure the Value to be of this format [“value1”,“value2”,…] where every item in this list corresponds with a value from the change that should be stored in the Excel file.
    
    
    1. This string should be built value by value.
       
       Start by typing [“ and then click to add the first value Product ID (A)
       
       

       

       
       
    2. Add another double quote, a comma, and a new double quote (",") for the next value:
       
       

       

    3. Repeat steps a and b to add all the values.
       
       Finally, add the user’s name from the Get User block as the final value.
       
       Optionally, this can also be another indicator, such as an email address. Finish the operation with another square bracket and a trailing comma ("],).
       
       
       
18. Add an Add Rows To Table (Batch) block from the Microsoft Excel connector after the loop from the Loop block.
    
    This block will be used to update the Excel file with the generated string that represents the current batch of changes.
    
    Configure the block as follows:
    
    
    • Drive Id: Drive Id from the “Parent Reference” from the Lookup Item In List block
    • Item Id: Id from the Lookup Item In List block
    • Worksheet: Sheet Name variable
    • Table Id: Table Name variable
    • Rows: This will be the Rows String variable, but it is not fully ready yet and needs some modifications:
      
      
      1. Add the Rows String variable
         
         

         

      2. Click the field mapping to the variable and choose Add formula. This will open the formula picker.
         
         

         

      3. Search for the Right trim formula.

         
         

      4. Configure the Character to trim parameter to a single comma.

         

      5. Type a single square bracket after the field mapping in the Rows input field:

         
         
         
19. Add a Close Workbook Session block from the Microsoft Excel connector at the end of the automation after the loop from the Loop Batch block.
    
    Configure the block as follows:
    
    
    • Drive Id: Drive Id from the Parent Reference from the Lookup Item In List block
    • Item Id: Id from the Lookup Item In List block
    • Session Id: Id from the output of the Create Workbook Session block
      
      
      
20. Run the automation manually and review the generated Excel file to make sure the table in the order form is correctly populated.
    
    
    
    
    

Running the automation from the sheet

The automation is now configured and can be run manually. But ideally, a user can run it from within the Qlik Sense app whenever they are finished with creating orders through the Write Table.

This article will only cover the button’s configuration in a sheet. A step-by-step guide on configuring the button object to run automations is available in How to run an automation with custom parameters through the Qlik Sense button.

1. Add a Button object to the sheet that contains the Write Table, set the button's action to 'Execute automation', and configure the automation.
   
   Tip! The automation selector in the button object only returns the first 100 automations. If your automation is not shown, you might need to manually copy and paste the automation id from the automation URL.
   
   
2. Add a parameter for the weekStart input from the automation's Inputs block.
   
   Then configure it to use the GetFieldSelections formula for the [Order Date] field (this is used as Week start in the Write Table).
   
   
   
3. Select a date in the app and click the button to run the automation and ensure that the correct date is received by the Inputs block:
   
   
   
   

Bonus: overwriting an existing Excel file

The Copy File block will fail when there already exists an Excel file with the same name. Depending on the use case, that might be the right behavior or you might want to overwrite the file.
The overwrite process explained below will delete the existing file and then create a new file.

1. Go to the automation and disconnect the Open File on Microsoft SharePoint block from the  Variable - tableName block.
   
   
2. Search for the Check If File Exists block from the Cloud Storage connector and connect it to the Variable - tableName block.
   
   Configure the block as follows:
   
   
   • Connector: Microsoft SharePoint
   • Path: folder path + / + destinationFileName variable (this should be the same path as the one configured in the Copy File block)
   • Drive Id: driveId variable
     
     
     
     

3. Add a Condition block to the automation and configure it to evaluate the output from the Check If File Exists block.
   
   This block will return a Boolean (true or false) result. If it is true, the file exists.
   
   Configure the Condition block to evaluate that output using the Boolean 'is true' operator:
   
   

4. Add a Delete File block from the Cloud Storage connector to the 'Yes' part of the Condition block. This will then only be executed when a file already exists, and will then delete the file. Configure the block as follows:
   
   
   • Connector: Microsoft SharePoint
   • Path: folder path + / + destinationFileName variable (this should be the same path as the one configured in the Copy File block)
   • Drive Id: driveId variable
     
     
     
     
5. Collapse the 'No' part of the Condition block as this will not be used (when there is no file, the automation can continue and copy the template to create a new file). Right-click on the Condition block and select the 'Hide NO Condition' option.
   
   
   
   

   
   

6. Reconnect the Open File block (and the other attached blocks) to the Condition block.
   
   

   

7. Perform two runs of the automation for the same date (weekStart) and ensure the original file is deleted and then recreated.
   
   
   
   
   

Bonus: Sending the Excel file as an email attachment

Qlik Automate can also be used to share the purchase order with your purchasing team. This can be built in the same automation or in a separate automation. Below are the steps to add this to the same automation.

1. Add an Open File block from the Cloud Storage connector at the end of the automation.
   
   Configure it as follows:
   
   
   • Connector: Microsoft SharePoint
   • Path: folder path + / + destinationFileName variable
   • Drive Id: driveId variable
     
     
     
2. Add a Send Mail With Attachments block from the Microsoft Outlook 365 connector.
   
   This block will be used to send the Excel file as an email attachment to one or more recipients.
   
   Configure the block as follows:
   
   
   • To: one or more email addresses for the recipients of the purchase order
   • Subject: Purchase order { $.inputs.weekStart }
   • Type: text
   • Content Body: <your email body>
   • Attachments:
     
     
     1. Click the 'Add attachment' button
     2. Specify the output from the Open File 2 block
        
        
        
        

Tip! Update the button label to make it clear to users of your app that clicking it will also send the purchase order.

As an alternative, it is also possible to add these blocks to a new automation that is triggered from a second button.