How to use access filters in derived tables

Knowledge Drop

Last tested: Oct 31, 2019
 

Question

How do I use access filters inside the SQL of a derived table?

An Answer

All that access filters do is apply a user attribute value to a field as a condition. So, basically, just write a WHERE condition in the derived table, calling the user attribute via Liquid.

Example

If you have an access filter like so:

 

access_filter: {

field: department

user_attribute: dept

}

and derived table SQL like so:

sql: SELECT * FROM table ;;

then you can simply add a WHERE to the derived table SQL like so:

sql: SELECT * FROM table WHERE department = {{ _user_attributes['dept'] }} ;;

Disclaimer

Please note that the PDT Regenerator has no user attribute values, so it will input a blank for {{ _user_attributes['dept'] }} in the above example. For this reason, a derived table which references user attributes via Liquid should not be a PDT.

This content is subject to limited support.                

Comments
Om_Singh
Explorer

Power Of Derived Tables In Looker: A Game Changer For Data Analysis

https://dataanalysisexperts.net/power-of-derived-tables-in-looker-a-game-changer-for-data-analysis/

Version history
Last update:
‎07-07-2021 03:19 PM
Updated by: