HI
Im calculating the length between two dates as a number, and then i want to convert it into a string to be able to group the days. but when running the code below, I get the errormessage
“No matching signature for operator = for argument types: INT64, STRING. Supported signature: ANY = ANY”
What am I doing wrong here?
dimension: date_diff_day {
type: number
sql: date_diff(${init_date_date},${comp_date_date},day)*-1;;
}
dimension:date_diff_day_string{
type: string
sql: ${date_diff_day} ;;
}
dimension: date_diff_days_grouped {
type: string
sql: CASE
WHEN ${date_diff_day_string} = “1” THEN “0-1”
…..
ELSE ${date_diff_day_string}
END;;
}
Hi Alexander,
I think there are a few things which may be going wrong:
(1) date_diff only works with certain date formats, so you might need to actively cast ${init_date_date} and ${comp_date_date} to date.
(2) most dialects do not work with double quotes, so you would need to replace them with single quotes
(3) not an issue, more of a recommendation; date_diff_day field is numeric, so the ‘case when’ statement can use the numeric values as they are without needing the interim conversion to string:
dimension: date_diff_days_grouped {
type: string
sql: CASE
WHEN ${date_diff_day} = 1 THEN '0-1'
…..
ELSE ${date_diff_day}
END;;
}
Hope this helps! If you let me know the dialect, I can attempt a more explicit solution.
On a separate note, from a best practice perspective, it is recommended for date dimension groups to not include the `date` word within their names to avoid `date_date`, `date_time` etc.
Best,