How to send straight table data to email as HTML table using Qlik Automate

[AfeefaTk]

This article provides an overview of how to send straight table data to email as an HTML table using Qlik Automate.

The template is available on the template picker. You can find it by navigating to Add new -> New automation -> Search templates and searching for 'Send straight table data to email as table' in the search bar, and clicking the Use template option.

You will find a version of this automation attached to this article: "Send-straight-table-data-to-email -as-HTML-table.json".

Content:

• Full Automation
• Automation structure
• Related Content

Full Automation

 

Automation structure

The following steps describe how to build the demo automation: 

1. Add a variable called 'EmailBody' to build the email body, and append the body opening tag and opening text of the email as value to this variable.
   
   
2. Add another variable called 'EmailStraightTable' and append the table opening tag together with the table header opening tag as a value to this variable.
   
   
3. Use the 'Get Straight Table Data' block from the Qlik Cloud Services connector to get data from the straight table for a specific app to be sent as an email. Specify app ID, Sheet ID, and Object ID input parameters. You can use the do-lookup functionality to find value for all these parameters. There is a limit of 100,000 records that can be retrieved through the 'Get Straight Table Data' block. This block will not work with other table types.
   
   
4. Add a 'Loop' block to loop over the keys from the 'Get Straight Table Data' block response which act as the headers of the HTML table. Use the '{getkeys: {$.GetStraightTableData}}' formula in the 'Loop' block.
   
   
5. Add the key/column name as the header of the HTML table in the 'EmailStraightTable' variable block. Outside the loop, add the 'EmailStraightTable' variable block, and append the table header closing tag, and table body opening tag as values to this variable.
   
   
6. Add a 'Loop' block to loop over all the values from the 'Get Straight Table Data' block response. Within the 'Loop' block add the following blocks:
   
   
   1. Use the 'EmailStraightTable' variable block and append the table row opening tag as a value to this variable.
   2. Use another 'Loop' block to loop over all values from the straight table for the current key/ column of the straight table.
   3. Next, use the 'EmailStraightTable' variable block and append the table data opening tag along with the column value for the corresponding key from the straight table which ultimately gives a single row with values for all the keys in each iteration and the table data closing tag as value to this variable.
   4. Use the 'EmailStraightTable' variable block and append the table row closing tag as a value to this variable.
      
      
7. Add the 'EmailStraightTable' variable block and append the table body closing tag with table footer tags as a value to this variable.
   
   
8. Add the 'EmailBody' variable block and append the 'EmailStraightTable' variable, which contains the final HTML table with straight table data, together with the email footer, and body closing tag as a value to this variable.
   
   
9. Add a 'Send Mail' block from the Mail connector to send an email with the above constructed HTML table that contains straight table data. Specify values for all the mandatory input parameters. Please connect to the SMTP server by adding a connection to the Mail connector.

An example output of the email sent:

The information in this article is provided as-is and will be used at your discretion. Depending on the tool(s) used, customization(s)andor other factors, ongoing support on the solution below may not be provided by Qlik Support.

Related Content

How to export more than 100k cells using Get Straight Table Data Block 

Environment

• Qlik Cloud Analytics
• Qlik Automate

[AbdullahMastan]

@AfeefaTk  

Amazing automation.  

The {getkeys: {$.GetStraightTableData}}' formula in the 'Loop' block fetches all the column names from a table. 

I tried to get selective columns by using {$.GetStraightTableData.columnName}, subsequently I removed the upper loop block. This however is producing undesired results. 

Any insight on how to select specific columns? 

[mikecrengland]

I'm trying to loop this over one of my fields with no luck. I added 'List Value Of Field' and 'Loop' at the begining, then put the other blocks inside it. Any thoughts on what else I need?