6-minute video: https://www.youtube.com/watch?v=FQsTiMB9aX4

Files from Github.

Building Excel-styled tables with different formulae on each row has always been a pain point in Qlik.

We have all created Excel styled spreadsheet using Valuelist as a dimension and PICK MATCH in the expression in order to achieve the cell level control as it is in Excel.

However, this becomes hard to read and maintain when expressions in cells get extremely complex and also fidgety when you need to add new lines in an existing front-end Qlik table.

So I looked for an external way of maintaining these and am now sharing the framework and files to be able to easily create, maintain and change these Excel-style tables in Qlik.

Please watch the YouTube video for more ample information, explanation and instructions.

https://youtu.be/PIR9tsPAvjo

This is a quick an easy way of sending email alerts using the script editor during data reload process.

It utilizes Microsoft Flow which you have access to if you have Office 360 subscription.

It is a 2 step process. First we setup a MS Flow which accepts REST requests with three parameters(to, subject, body) and emails to these values to the address using first parameter value.

REST API is a one time setup that you can reuse within all of your scripts. No need to setup different one for each instances!

Then use a REST Call from script in the event of an error to send an alert.

1. Login to Office 365 and go in to flows.

2. Create a new flow using INSTANT FROM BLANK

3. click SKIP on this screen.

4. Search for REQUEST and choose WHEN HTTP REQUEST IS RECEIVED

5. Paste the following JSON body area choose POST in the ADVANCED area. Then click NEW STEP

{
    "type": "object",
    "properties": {
        "to": {
            "type": "string"
        },
        "subject": {
            "type": "string"
        },
        "body": {
            "type": "string"
        }
    }
}

6. For next step search EMAIL then choose Send an email (v2)

7. In the email properties, click on each property and choose the matching dynamic parameter name from the right

8. When finished, it should look like this.

9. Now SAVE.

10. Once  saved you will see the request URL. COPY THIS URL 

Now in you application create a new REST connection using the URL you just copied.

Now you just have to insert this in your script to send email alerts for load failures.

//....INSERT THIS INTO BEGINNING OF YOUR SCRIPT

SET ErrorMode = 0;



If ScriptErrorCount > 0 then

// .....SET THESE 3 Variables before trunning

     LIB CONNECT TO 'YOUR_REST_CONNECTION_NAME';
     SET vAPI_URL = "https://YOUR_MICROSFOT_FLOW_API_URL";
     SET vEmail = "Target@emailAddress.com";


     LET vRequestBody = '{ "to": "$(vEmail)", "body": "AppID=' & DocumentName() & '<br>'
& 'DocTitle=' & DocumentTitle() & '<br>' & 
'Error=' & ScriptErrorList & '<br>~ ' & ScriptError 
& '", "subject": "ReLoad Error='& DocumentTitle() & '"}';
     LET vRequestBody = replace(vRequestBody,'"', chr(34)&chr(34));

     RestConnectorMasterTable:
     SQL SELECT 
     "col_1"
     FROM CSV (header off, delimiter ",", quote """") "CSV_source"   WITH CONNECTION (
     BODY "$(vRequestBody)",
     Url "$(vAPI_URL)"
     );

     DROP TABLE RestConnectorMasterTable;

     exit Script;
end if

That's It. You now should get an email alert every time there is a failure with details of the error and the APPID & name.