Question

Yesno for data

  • 24 September 2021
  • 0 replies
  • 19 views

Dear Colleagues, I have a question.

I have column with string. I do like this:

dimension: main_table_boolean {
type: yesno
sql: ${TABLE}.table_name LIKE '%ord%';;
}

dimension: main_table {
type: string
sql: case when ${main_table_boolean} then 'main' else 'not_main' end ;;
}

It gives me answer:

 

Missing Data Main Table

Missing Data Average Percent

1 not_main 7.404985591339665
2 main 5.748898156787999

    

Perfect!

But if I need to do the same with date column it does not work. I do like this:

dimension: last_date_boolean {
type: yesno
sql: ${TABLE}.last_date_in_batch = MAX(${TABLE}.last_date_in_batch);;
}

dimension: new_old_data {
type: string
sql: case when ${last_date_boolean} then 'new_data' else 'old_data' end ;;

}

And Looker gives me error: aggregate functions are not allowed in GROUP BY

I suppose it means MAX function. Please advice is there a way to avoid such a behavior of Looker?

Many thanks!

Sergio


This topic has been closed for comments