Add Hours based on different conditions (Case statements)

[kadiwalanaeem]

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})
;;
}

[Dawid]

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?

[kadiwalanaeem]

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

[Dawid · Accepted 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

[kadiwalanaeem]

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;;
}