Hi Community!
I am bit new with lookml So please bear with me :). I want to create dimension_group where in i want to add hours based on different condition. can any one please help me. Below is my sample code.
dimension_group: added_hour_date{
description: added_hour_date
type: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: case
when ${type}=‘X’ Then {% add_hours(2,${arrived_date}) %} #here i am confused
when ${type}=‘Y’ Then {% add_hours(3,${arrived_date}) %} #here i am confused
else ${arrived_date})
;;
}
Solved! Go to Solution.
If you were to do it in pure SQL, just by connecting to your source, what would the query look like? Would it be something like this:
CASE
WHEN ptype =‘X’ THEN arrived_date + 2 HOURS
WHEN ptype=‘Y’ THEN arrived_date + 3 HOURS
ELSE arrived_date
END
If so that’s all you need to do, of course the arrived_date + 2 HOURS
needs to be adopted to your database
I think you’re trying to put Liquid syntax where it’s not necessary if my assumption is correct that your SQL can see ${type}
natively in the database. Does it come from the same source?
Would you be able to run the query you put in sql
parameter in your database and it would be fine?
yes ${type} is coming from same source. Also the actual column is ${ptype} so it will not conflict.
Also for the {% add_hours(2,${arrived_date}) %} i have just got from other forum so i have used it. But i want to know that how can i add particular hours in ${arrived_date} based on ${ptype} and create a dimension_group
If you were to do it in pure SQL, just by connecting to your source, what would the query look like? Would it be something like this:
CASE
WHEN ptype =‘X’ THEN arrived_date + 2 HOURS
WHEN ptype=‘Y’ THEN arrived_date + 3 HOURS
ELSE arrived_date
END
If so that’s all you need to do, of course the arrived_date + 2 HOURS
needs to be adopted to your database
Hi Guys,
Have got a solution of above query. I am posting it here to help others so that they can get solution easily.
dimension_group: added_hour_date
{
description: added_hour_date
type: time
timeframes: [
raw,
date,
week,
month,
quarter,
year ]
sql: case
when ${type}=‘X’ Then dateadd(minute, 120, ${TABLE}.arrived_date)
when ${type}=‘Y’ Then dateadd(minute, 240, ${TABLE}.arrived_date)
else {TABLE}.arrived_date
End;;
}