Qlik Application Automation (Part 2)

Introduction and Overview

In this live stream, the presenters discuss app automation using Click application automation. They recap the previous episode where they used Click application automation to generate QVDs from Google Sheets and an open API called AlphaVantage. The goal is to create a ClickSense app that helps with tax loss harvesting by analyzing share performance.

App Automation Build Series

• Recap of previous episode where QVDs were generated from Google Sheets and AlphaVantage.

• Objective is to create a ClickSense app for tax loss harvesting.

Introduction to the ClickSense App

The presenters introduce the ClickSense app that will be used as the basis for creating automations. They explain that this app can be similar to ones already built in other workplaces or personal projects. The goal is to enhance this app by adding new automations.

ClickSense App Overview

• Standard ClickSense app with sheets and charts.

• Contains dimensions and measures for analysis.

• Data loaded from QVDs generated in previous session.

• Additional sheet for generating PDF reports.

First Automation - Updating Currency Exchange Rates

The first automation focuses on updating currency exchange rates within the ClickSense app. The presenter demonstrates how to use the AlphaVantage API to retrieve exchange rate data and update a variable in the app.

Updating Currency Exchange Rates

• Use AlphaVantage API to retrieve exchange rate data.

• Create a new automation using Call URL block.

• Specify JSON object with key-value pairs for API parameters.

• Example: USD to GBP exchange rate.

• Retrieve JSON response containing exchange rate data.

• Use Update Variable block from Click Cloud Services connector to update variable in the app.

Handling Invalid JSON Format

The presenter encounters an error due to an invalid JSON format in the Update Variable block. They explain that dots are reserved characters in JSON and need to be handled properly.

Handling Invalid JSON Format

• Error encountered due to invalid JSON format.

• Dots are reserved characters in JSON.

• Need to modify the key name containing dots in the JSON object.

Best Practices for Creating Variables

The presenter discusses best practices for creating variables in ClickSense and emphasizes the importance of ensuring correct values before making variables dynamic through automation.

Best Practices for Creating Variables

• Create variables within ClickSense app before making them dynamic through automation.

• Verify values and ensure correctness before automation.

• Avoid confusion by following best practices.

Updating Variables in Qlik Sense Apps with Automations

In this section, the speaker demonstrates how to update variables in Qlik Sense apps using automations. They explain the concept of sessions and why automations use a different session than the UI. The speaker also introduces the "Save App" block, which allows pushing changes from automations to other sessions.

Updating Variables in Qlik Sense Apps

• Automations use a different session than the Qlik Sense UI to avoid applying selections made in automations to the UI.

• The "Update Variable" block can be used to update variables in Qlik Sense apps within an automation.

• To push changes from an automation to other sessions, the "Save App" block is used.

• The "Save App" block allows configuring which blocks' changes should be pushed to other sessions.

Setting Up Variable Updates

• Add a "Save App" block and configure it for the desired app and tab.

• Execute the automation to see if the variable was successfully updated.

Using Automation Variables vs Click Variables

• Automation variables are separate from click app variables and are scoped only within automations.

• It's important to differentiate between using an automation variable and updating a click variable.

Copying Blocks and Multiple Executions

This section covers copying blocks within an automation and limitations on using multiple "Save App" blocks for the same app within one automation run.

Copying Blocks Within Automations

• Right-click on a block and select "Copy Block with Attached Blocks" to copy all connected blocks into your clipboard.

• Paste copied blocks into another automation or within the same automation, making necessary configuration changes.

Limitation on Multiple "Save App" Blocks

• Only one "Save App" block per automation run per app is allowed.

• Using multiple "Save App" blocks for the same app in one automation run will only consider the first execution.

Updating Variables Frequently with Automations

This section explains how to set up automations to update variables frequently without having to reload the entire Qlik Sense app.

Setting Automation Run Mode to Scheduled

• Configure the "Start" block's "Run Mode" parameter to schedule automation runs.

• Specify the desired frequency, such as every five minutes, based on your use case.

• Save the automation and it will be executed automatically at the scheduled intervals, updating variables without requiring manual reloads.

Setting Price Alerts with Automations

In this section, the speaker introduces setting price alerts using automations. They mention a master item called "Return Percentage Today" that tracks changes in stock positions over the past 24 hours.

Setting Up Price Alerts

• Create a master item called "Return Percentage Today" to track changes in stock positions over 24 hours.

• Use set analysis instead of a normal measure to retrieve the latest date for a particular stock.

Conclusion

The transcript covers updating variables in Qlik Sense apps using automations. It explains sessions, demonstrates how to update variables, push changes using the "Save App" block, copy blocks within automations, and set up frequent variable updates. Additionally, it introduces setting price alerts with automations using a master item.

New Section

This section discusses the calculation of a measure for one stock at a time and the ability to validate it across the entire portfolio or for individual stocks.

Applying Selection and Validating Measures

• To validate measures, a selection needs to be applied.

• The selection can be applied globally or for individual stocks.

• The selection only applies during the automation session and has no effect outside of it.

New Section

This section focuses on setting up alerts for individual stocks based on reaching certain thresholds.

Setting Up Alerts

• Alerts can be set up to monitor if individual stocks reach specific thresholds.

• These alerts can trigger actions such as sending notifications to Slack.

New Section

This section explains the process of applying selections to specific stocks.

Applying Selections

• Selections need to be applied to the desired stock.

• In this example, Tesla is selected as the stock for comparison.

New Section

This section demonstrates using the "Get Measure Value" block after applying selections.

Using "Get Measure Value"

• After applying selections, use the "Get Measure Value" block.

• The example measure used here is "Return Percentage Today."

New Section

This section introduces using a condition block to validate measure values and take action based on them.

Validating Measure Values

• Use a condition block (similar to an if statement) to validate measure values.

• In this case, if the output of "Get Measure Value" is more than 15%, take some action (e.g., send an alert).

New Section

This section demonstrates sending an alert to a Slack channel when a stock's value exceeds the threshold.

Sending Alerts

• Use the "Send Message" block to send an alert to a Slack channel.

• The message can include information about the stock and its percentage increase.

New Section

This section explains how to format the percentage value before sending it in the alert message.

Formatting Percentage Value

• Use formulas (e.g., multiplication, rounding) to format the decimal value as a percentage.

• Click offers a formula picker with various functions for data manipulation and formatting.

New Section

This section demonstrates receiving an alert message on Slack after executing the automation.

Receiving Alert Message

• After executing the automation, check for the received message on Slack.

• The message should indicate the stock's percentage increase.

New Section

This section discusses enhancing the informative nature of the alert message by including additional details using external APIs.

Enhancing Alert Message

• Utilize external APIs (such as Stock News API) to include news articles related to stocks with significant changes.

• Use API calls and URL mapping within Click to retrieve and display relevant news articles in Slack.

New Section

In this section, the speaker discusses executing automation and sending messages for stock analysis.

Executing Automation and Sending Messages

• The speaker executes the automation again.

• Three messages were sent, and the articles in Music and Tesla are previewed.

• This approach currently only works for Tesla as the stock is hard-coded.

• To execute this for every stock individually, the first step is to list every stock in the portfolio using the "List Values of Fields" block.

• The "List Values of Fields" block returns all unique values of a certain field.

• The symbols are listed inside a loop to execute the automation for each stock individually.

• The Q text value of the field is used to apply selection and update the description.

• Toggling formula forcing helps identify spaces when concatenating JSON bots with strings.

• The call URL block is updated to reflect the current stock symbol (Q text value).

New Section

In this section, the speaker discusses executing automation based on web hook events.

Executing Automation Based on Web Hook Events

• Due to limited tokens in their subscription for Stop News API, they decide not to execute further actions at this point.

• Messages are coming in after executing previous actions.

• There were nine stocks with a higher return than 15 percent.

• Facebook and Zoom had significant activity according to Slack channel observations.

• The speaker suggests executing the automation every time there is a new reload in the application.

• A web hook event is created to listen to the "app reload finished" event for a specific app ID.

• The automation will be executed whenever the specified app is reloaded.

New Section

In this section, the speaker discusses creating a weekly report sheet and using click reporting connector for reporting purposes.

Creating Weekly Report Sheet and Using Click Reporting Connector

• The last automation focuses on Simon reports, specifically a weekly report sheet.

• The weekly report sheet contains data without user inputs, suitable for sending as a PDF report.

• The speaker creates a new automation to demonstrate the visibility of hidden sheets within automations.

• For reporting purposes, they use the Click Reporting Connector to access Click's reporting services.

• To trigger the reporting process, a report block needs to be created with the desired app and name.

• Custom range selection is used to include hidden sheets in the report.

• Sheets can be added to the report using lookup blocks and dragging them into place. Additional selections can also be added globally or on a sheet level.

Due to limited information provided in this transcript, further sections could not be summarized accurately within the given guidelines and word limits.

Generating Reports and Cloud Storage Connectors

In this section, the speaker discusses how to generate a report using the "Generate Report" block and how to use cloud storage connectors to store the output file.

Generate Report Block

• The "Generate Report" block finalizes all the configurations made in previous blocks and creates a PDF file containing the report.

• The block is represented by a file icon at the top.

• The generated report can be accessed through cloud storage connectors.

Cloud Storage Connectors

• By using connectors like "Copy File," you can copy the received file from reporting services to various destinations.

• This allows for making reports available in different locations.

• For example, you could send it to a file storage platform or use other connectors like mail connector.

Selection State with Reporting Connector

This section explains how the selection state works with the reporting connector compared to standard click cloud services connectors.

• The selection state is built up inside of the reporting connection, separate from other blocks.

• It is not shared with other blocks that use click cloud services connectors.

• The selections are added to the definitions of reports and sheets before being sent to reporting services for compilation.

Making Multiple Selections in Reports

Here, multiple selections in reports are discussed, along with an example of filtering by date.

• To apply multiple selections efficiently, use click cloud connectors like "Select Field Values by Query."

• This block allows for making multiple selections at once.

• For example, you can filter data based on dates using specific formats or ranges.

• After making selections, you can retrieve them using the "List Current Selections" block.

Sharing Automation with Others

This section addresses how to share an automation with users who are not the owners.

• Users can run an automation if they have the trigger URL.

• To make an automation executable by others, set the appropriate permissions for sharing and access.

• The automation can be scheduled to run at specific intervals, such as weekly or daily.

• When sending emails through the automation, configure your own email SMTP server and select the desired connection.

The transcript is already in English.

New Section

In this section, the speaker discusses the functionality of a platform and demonstrates how to add a button to execute an automation within an app or sheet.

Adding a Button for Automation Execution

• The platform offers MacBook functionality and the ability to schedule tasks.

• A button can be added inside the app or sheet to execute an automation.

• The speaker demonstrates adding a button object from the Charts menu.

• It is suggested not to replace existing objects when adding the button.

• The title of the button is not shown in the demonstration.

Executing an Automation with a Button Click

• Under Actions and Navigation, another action can be added.

• To execute an automation, select the desired automation (e.g., "Stock Reporting").

• When the button is clicked, the selected automation will be executed.

• This feature allows any user of the application to send an email by clicking on the button.

Considerations for Email Sending

• There may be restrictions on SMTP servers regarding file size limitations for sending over SMTP.

• It is recommended to use a default Gmail address without too many restrictions during development or share PDF files via SharePoint and send a link instead of attaching large files in emails.

Customizing Email Recipients

• Instead of hard-coding email addresses, it is possible to include a selection field in the button where users can choose an email address.

• The selected email address can be included as an output of the automation using the store block.

• It may be possible to use section access and check if the user's email address is listed as a field value to send personalized emails. This approach requires further experimentation.

Sharing Automation Files

• The speaker mentions that they will share the automation files and KVF file after the session.

• To import an automation file, create a blank automation, right-click on the canvas, and select "Upload Workspace" to load the file.

• Automations are currently owned privately but will soon be available for sharing on the roadmap.

Summary

The speaker demonstrates how to add a button for executing automations within an app or sheet. They also discuss considerations for sending emails with attachments and suggest alternative methods such as using SharePoint links or customizing email recipients through selection fields. Additionally, they mention sharing automation files and upcoming features on the roadmap.

Timestamps have been associated with relevant bullet points based on their provided timestamps in the transcript.