Converting number to string

[Alexander_Arozi]

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

[Maddie]

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,