May 2, 2023 2:47:25 AM
Currently, in Qlik Application Automation it is not possible to export more than 100,000 cells using the Get Straight Table Data block.
Content:
To overcome this limit, the workaround is to export records in batches from the Qlik Sense straight table to the cloud storage platform of your choice. The prerequisite is to have a unique numerical field in your dataset. If you don't have the unique field in your dataset, you can add it using RowNo() function in the load script as shown below. This counts the rows in the dataset.
In this example, we will export data from the Qlik Sense straight table to Dropbox as a CSV file.
You can also find an exported version of this automation and application attached to this article. More information on importing automation can be found here.
Full Automation
Steps to set up the variables
- vAppId: Variable used to define your app ID.
- vSheetId: Variable used to define your sheet ID.
- vTableId: Variable used to define your straight table ID.
- vFieldName: Variable used to define the field name on which the straight table data should be filtered based on the selection applied using the Select Field Value By Query block.
Automation structure
- Add the Create Measure block to the automation and configure the Measure Expression parameter by providing an expression that calculates the number of rows in the dataset. In this example, I have added RowNo( ) as a unique field to the dataset, so the expression used is count([RowNo( )]).
- Add the Get Measure block to get the number of rows in the dataset.
- Store the number of rows obtained from the previous step in the vNRecords variable.
- Delete the measure created using the Delete Measure block.
- Add the vNLoops variable to calculate the number of loops that can be used to export straight table data in batches. Please note the formula used in the variable. Edit it to suit your needs. Change the 10000 value as you want but keep in mind that too many records could exceed the Get Straight Table Data block's 100,000 cells limit.
- The Custom Code block creates a list with items 1 to n where n is the number of loops calculated in the previous step.
- Add Create File on Dropbox block to create a new CSV file in Dropbox. You can select any other cloud storage connector, just remember to configure the relevant value in the Path parameter. The Qlik Sense straight table data will be exported to this file.
- Add the Select Field Values By Query block to apply selection to the unique identifier created in the dataset to fetch only one record from the Get Straight Table Data block.
- Add Write Line To File on Dropbox block to add straight table keys as column headers to the CSV file.
- Add Loop block to loop over the list created using the Custom Code block.
- Add Condition block to check if it's iterating over the 1st item, if yes set vRecordsStart variable value to 1, else set vRecordsStart variable value to vRecordsEnd variable. The vRecordsEnd variable represents the last record that needs to be fetched from the straight table during each iteration. Please note the formula used in the variable. The value under the Value 1 parameter in the Multiply formula should match the value provided in the Divide formula of the vNLoops variable.
- Add the Select Field Values By Query block to apply selection to the unique identifier created in the dataset to fetch the records between vRecordsStart and vRecordsEnd variables from the Get Straight Table Data block.
- Add Write Line To File on Dropbox block within the Get Straight Table Data block which writes each straight table data into the CSV file. Configure it to use the output from the Get Straight Table Data block as the Data parameter.
- Add Save And Close File on Dropbox block and configure the file parameter to use the CSV file we have created.
Environment
