Knowledge Drop

How to write a PDT Trigger Value that rebuilds multiple times per day

• 0 replies
• 43 views

Userlevel 5
• Looker Staff
• 169 replies

Last tested: June 2019

The Problem

I want to set a PDT Trigger value which rebuilds twice a day. The docs only say how to build once a day! In this example, let's say I want to build at 0900 and 1700 daily.

A Solution

When approaching trigger problems, it helps to envision a table of desired values. In this case, you want to make sure that the trigger value changes at 0900, but doesn't change back at 0901. It needs to be exactly the same value from 0900 to 1659, then change at 1700, then be the same value from 1700 to 0859 the next day. In tabular form:

 Time Value 0859 0 0900 1 0901 1 1659 1 1700 0 1701 0

We can achieve this pretty easily with a CASE WHEN!

``CASE WHEN [current time is between 0900 and 1700]``THEN 1 ``ELSE 0``END``

General Form

Generally, for n desired times:

``CASE``WHEN [current time is in between time 1 and 2] THEN 1 ``WHEN [current time is in between time 2 and 3] THEN 2``...``WHEN [current time is in between time _n-1_ and _n_] THEN _n-1_``ELSE _n_ ``END``

This content is subject to limited support.