Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Feb 23, 2023 1:54:12 AM
Apr 29, 2022 8:01:44 AM
This article explains how to import and export master items to and from a Qlik Sense app using the Microsoft Excel connector in Qlik Application Automation.
Content:
- Export master items to a Microsoft Excel sheet
- Import master items from a Microsoft Excel sheet
- Edge cases & next steps
The first part of this article will explain how to export all of your master items configured in your Qlik Sense App to a Microsoft Excel sheet. The second part will explain how to import those master items from the Microsoft Excel sheet back to a Qlik Sense App.
Export master items to a Microsoft Excel sheet
For this, you will need a Qlik Sense app in your tenant that contains measures, dimensions, and variables you want to export. You'll also need an empty Microsoft Excel file. The image below contains a basic example on exporting master items.
The following steps will guide you through recreating the above automation:
- Add the Create Workbook Session block from the Microsoft Excel connector. Configure it with the following settings:
- Drive id -> use do lookup
- Item id -> use do lookup to find the empty destination file, if you don't know the path of your file, you can do an empty search (if it isn't located in a folder)
- Add the Add Worksheet block from the Microsoft Excel connector. Use the same Drive Id & Item Id from the previous step and configure the Name parameter to a string of your choice. In this example, we'll use Measures.
- Add the Create Excel Table With Headers block from the Microsoft Excel connector. This block will create a table inside the sheet from the previous step. Specify the following in the block's configuration:
- Start Row -> 1
- Start Column -> A
- End Column -> E
- Headers -> Field,Name,Label Expression,Description,Tags,Measure Color, Segment Color, Number Format
- Name -> Measures
- Add a List Measures block from the Qlik Cloud Services connector and also a Get Measure block inside the loop created by the List Measures block, configure it to get the current item in the loop. Add two variables to convert Gradient and NumFormat JSON objects to string.
- Add an Add Row To Table block from the Microsoft Excel connector inside the loop after the Get Measure block. This will add every measure's information to the table one by one. Set the Drive Id, Item Id, Worksheet, and Table Id to the corresponding values from the previous blocks.
The Row parameter should be an array of values for every header we specified in the Create Excel Table With Headers block (and in the same order). Title, Label expression, and Description should be encapsulated in double-quotes. Apply the JSON encode formula to the value for the Definition (qDef) and apply the Implode formula to the value for the Tags. - Add a Close Workbook Session block from the Microsoft Excel connector. Specify the same Drive Id & Item Id as in the previous blocks and configure the Session Id to the Id returned by the Create Workbook Session block.
An export of the above automation can be found at the end of this article as Export master items to a Microsoft Excel sheet.json
Import master items from a Microsoft Excel sheet
For this example, you'll first need a Microsoft Excel file with sheets configured for each master item type (dimensions, measures, and variables). Use the above example to generate this file. The image below contains a basic example on importing master items from Microsoft Excel to a Qlik Sense app.
- Add the List Rows With Headers block from the Microsoft Excel connector to read master items from the Excel file. Configure the block with the following settings:
- Drive Id -> use do lookup
- Item Id -> use do lookup to find the empty destination file, if you don't know the path of your file, you can do an empty search (if it isn't located in a folder)
- Worksheet Name -> the name of the sheet that contains the measures. Feel free to use do lookup
- Start Cell -> the upper-left cell of the measures table, this should include the header row, for example, A1
- End Cell -> the bottom right cell of the measures table, for example, E23
- Add a Condition block inside the loop created by the List Rows With Headers block. The condition will verify that every measure row has the required information to create a measure (name and expression).
See the below image for an example: - Add the Create Or Update Measure block from the Qlik Cloud Services connector to create or update the measure in the destination app. Map each field from the row in the Excel table to the corresponding input field in the Create Or Update Measure block. Measure Id can be left empty since we're matching measures by Name (Measure Ids can be different across apps). See the below image for an example:
An export of the above automation can be found at the end of this article as Import master items from a Microsoft Excel Sheet.json
Follow the same steps to build automations that import/export dimensions and variables.
Edge cases & next steps
Let's go over some edge cases when exporting information to Microsoft Excel:
- Fields that start with an equals-sign '=' (for example, some variables' definitions) are treated as Excel functions and can be deemed invalid by the Excel API. You can resolve this by adding a single quote before the input field's mapping in the automation.
- Fields that contain newlines (for example measure expressions that contain comments) are invalidated by the Excel API. The solution here is to use the JSON formula to encode the string.
Please check the following articles for more information about working with master items in Qlik Application Automation and also uploading data to Microsoft Excel.
- How to get started with Microsoft Excel
- Distribution of Master items using Qlik Application Automation
- Uploading data to Microsoft Excel
Follow the steps provided in this article How to import & export automations to import the automation from the shared JSON file.
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
