Creating alert in SQL runner

[akidwell]

I created a nice graph in the SQL runner showing the number of customers added per day at my company. I tweaked it slightly which required me to do this in SQL runner rather than the explore tab. I would like to create an automatic alert when a certain number of customers is reached / day but the alert functionality only seems possible in the explore tab. Do you have any advice how I can create alerts in SQL runner. 

Thanks, 

Alex

[leobardor]

Hi Akidwell,

The closest SQL aprroach you can try is by creating a Looker- Derived table (see SQL-based derived table version here).

In this example a Postgres DB was used to create the SQL statement that is going to check if there are more than 1 check out row every day :

(SQL validation and dialect might be different from your datasource)

view: view_current_day {
  derived_table: {
 sql: SELECT date_of_discharge FROM hospital.patient_history
  WHERE date_of_discharge = current_date ;;
}
dimension:date_of_discharge {
  type: date
}
measure:counter_alert {
  type: number
    sql: count(date_of_discharge) ;;
  }
}

Notice that "current_date”  is a value retrieved from your datasource.

After creating the derived table, create a new explore in your model file  with 0 Looker - Persist .

For Instance:

explore: view_current_day {
  persist_for:"0 hours"
  }

After saving the changes in your LooMKL ( See Looker - Git control ) 

Create a new tile in any dashboard related to your project, for instance:

Your new tile should look like this:

In the next step, you must disable “development Mode” , otherwise you will not be able to see the alert icon:

In this step, the alert notification is set based on your counter measure:

(In this example, if the number of check-out is greater than 2 patients per day, an email is going to be send)

In this doc  Looker - Alerts  you may find additional settings and custom parameters.

Regards,

Leo

[akidwell]

Thanks very much! Really helpful!