Add calculated field in existing view using derived table

lookml

(Vishal) #1

Hi ,
I’m trying to create a new flag within existing view in lookML using calculated field built by SQL query .
This new field should be added as a dimension within existing view . It looks like I’m running into syntax issues… can any one help.

Here is syntax:
view: New_Traveler_Flag {
derived_table: {
sql:
select snapshotdate,travelerfirstentrysourcedate , cast(snapshotdate as date) - interval ‘7’ day New_Trav_Date
from
DbName.Traveler_Fact
where travelerfirstentrysourcedate != ‘9999-12-31’
and snapshotdate >= cast(snapshotdate as date) - interval ‘60’ day;;
}
dimension: new_traveler {
type: number
sql: CASE WHEN cast(${TABLE}.travelerfirstentrysourcedate as date) >= ${TABLE}.New_Trav_Date THEN 1 ELSE 0 END;;
}
measure: count {
type: count
}
}


(david.hughes) #2

Hi Vishal,

Thanks so much for your question. We can certainly help you fix your issue.

However, this type of question would be better suited to our live chat or our email support as we would really need to gather some more information about the error you are seeing, which may require some back and forth between us.
We would be able to provide you with an answer more efficiently through either of these channels.

Could I ask you to jump on our super helpful live chat or email us at support@looker.com with your question? and we will get your issue sorted as soon as possible.

Also what would be really helpful is to send us a screenshot of the error you are seeing, if you are sending a email to support@looker.com.

Thanks Vishal.

Regards,
Dave