Knowledge Drop

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

  • 26 June 2021
  • 0 replies
  • 43 views

Userlevel 5
Badge
  • 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 0END

General Form

Generally, for n desired times:

CASEWHEN [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.                


0 replies

Be the first to reply!

Reply