How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation

[AfeefaTk]

Currently, in Qlik Application Automation it is not possible to export more than 100,000 cells using the Get Straight Table Data block.

Content:

• Full Automation
• Steps to set up the variables
• Automation structure

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

Automation Part 1

Automation Part 2

Automation Part 3

Steps to set up the variables

1. vAppId: Variable used to define your app ID.
2. vSheetId: Variable used to define your sheet ID.
3. vTableId: Variable used to define your straight table ID.
4. 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

1. 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( )]).
2. Add the Get Measure block to get the number of rows in the dataset.
3. Store the number of rows obtained from the previous step in the vNRecords variable.
4. Delete the measure created using the Delete Measure block.
5. 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.
   
   
   
   
6. The Custom Code block creates a list with items 1 to n where n is the number of loops calculated in the previous step.
7. 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.
8. 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.
9. Add Write Line To File on Dropbox block to add straight table keys as column headers to the CSV file.
10. Add Loop block to loop over the list created using the Custom Code block.
11. 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.
    
    
    
    
12. 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.
13. 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.
14. Add Save And Close File on Dropbox block and configure the file parameter to use the CSV file we have created.

Environment

• Qlik Cloud
• Qlik Application Automation 

[epatinos]

Hey there @AfeefaTk , Im trying  to use this automation, with the example dataset, and I can't make it work.. I got the same error: "Object data can't be bigger than 100.000 cells"..

Can you give me a hand with it? 😞

[AfeefaTk]

Hi @epatinos 

Refer step 5

In vNLoops variable , reduce the value under Value 2 which is 10000 to a lower number. For testing purposes replace 10000 with 3000.

Also make appropriate changes to the vRecordsEnd variable (Step 11). As mentioned Value 1 parameter in the Multiply formula should be the same as that of the Value 2 parameter in the Divide formula of the vNLoops variable.

Please let me know if that works.

Thanks

[epatinos]

That work for me, I was also trying to select some fields before the measure, it seems that alterate the whole structure of the code..  

Thank you!!! 🙂

[Jenser55]

I tried to build the example by using your files. (QVF and json)

I don't know, what to enter in the variable vFieldName.

Could you help me please.

(For a newbie like me this tutorial contains to less explanation.) 

[skustes]

Hello, I am having trouble with the "Get Measure" block. I have attempted this two ways, as you described above using the "Create Measure" block, as well as by manually creating the measure within the app. The measure is called "row_count" with a formula of "count(row_number)", which is the alias I gave the RowNo() column in the data load. When used within the app in a KPI, the measure correctly indicates that there are 143 rows.

However, using "Get Measure" in an Automation doesn't return anything. I put "row_count" into the Measure Id field, but nothing happens. When I do a test run, there is an "In" section in History, but no "Out" section. Trying to set the returned value to the vNRecords variable produces a value of 0. The "In" section of the history in the Variable block says "Set vNRecords equal to <empty>"

Why is "Get Measure" not working for me?

[AfeefaTk]

Hi @skustes 

Thanks for reaching out.

Could you please share the automation workspace and the app for us to investigate the issue further?

Steps on how to export automation can be found here (Section: Download Workspace).

Regards

[AfeefaTk]

Hi @skustes 
If you have the RowNo() function added in the load script of the app, the formula that you need to use while creating the measure is Count([RowNo( )]).

I see you are using the incorrect formula "count(row_number)".

Could you please give it a try with Count([RowNo( )]) formula in the Create Measure block and see if that works?

Please let me know your feedback.

Thanks

Afeefa TK

[skustes]

Hi @AfeefaTk, I aliased the RowNo() function as row_number in the load. "RowNo() AS row_number". There is no problem within the app using "count(row_number)" to get the correct count. However, I opened a support case about the issue, so I will work through it via that channel.  Thanks!

[paulcalvet]

Hello,

I think this kind of automation works in shared space only.

Is there a workaround for managed space ?

thanks !

[paulcalvet]

Hello @AfeefaTk 

I try to do in different way and it works well.

The problem is it's very very long to export (52 minutes to export 16 000 lines) because it copy to file line by line.

Is there a way to improve performance like a bulk export ?

The goal is to allow users to automate their export data from corporate application to S3, and then they can use this dataset to build their own application in their shared space.

Thanks !