Can't figure out date filtering

zma213 · April 18, 2019, 6:53pm

I have a query that looks like this:

SELECT
agent_name,
channel,
message_count,
sla_time,
CASE WHEN channel = 'Chat' AND sla_time <= 30 THEN 'SLA Honored' WHEN channel = 'Chat' AND sla_time > 30 THEN 'SLA Breached' WHEN channel = 'Email' AND sla_time <= 15 THEN 'SLA Honored' WHEN channel = 'Email' AND sla_time > 15 THEN 'SLA Breached' WHEN channel = 'Chat and Email' AND sla_time <= 30 THEN 'SLA Honored' WHEN channel = 'Chat and Email' AND sla_time > 30 THEN 'SLA Breached' END SLA_status
FROM (
SELECT
agent_name,
channel,
message_count,
firstResponse_createdAt,
CASE WHEN channel = 'Chat' THEN bt_seconds WHEN channel = 'Email' THEN bt_minutes ELSE bt_seconds END sla_time
FROM (
SELECT
conversation_id,
customer_id,
agent_name,
message_count,
inbound_message_count,
outbound_message_count,
firstResponse_createdAt,
CASE WHEN channel = '["email"]' THEN 'Email' WHEN channel = '["chat"]' THEN 'Chat' WHEN channel LIKE ('%,%') THEN 'Chat and Email' END channel,
ROUND(bt_hour*60,2) bt_minutes,
ROUND((bt_hour*60)*60,2) bt_seconds
FROM (
SELECT
  conv.id conversation_id,
  relationships.customer.data.id customer_id,
  agent_name,
  attributes.custom.issueTypeTree	issueTypeTree,
  attributes.messageCount	message_count,
  attributes.messageCount -  attributes.outboundMessageCount inbound_message_count,
  attributes.outboundMessageCount outbound_message_count,
  DATE(attributes.firstResponse.createdAt) firstResponse_createdAt,
  attributes.firstResponse.time	firstResponse_time,
  ROUND(((attributes.firstResponse.time/3600000)/24) * 1,3) bt_days,
  ROUND(((attributes.firstResponse.time/3600000)) * 10,7) bt_hour,
  TO_JSON_STRING(conv.attributes.channels) channel
FROM
  kustomer.conversations conv
INNER JOIN (SELECT
id,
attributes.name agent_name
FROM
kustomer.users
LEFT JOIN UNNEST(attributes.roles) roles
LEFT JOIN UNNEST(attributes.roleGroups)
GROUP BY 1,2) users
ON users.id = conv.attributes.firstResponse.createdBy
  WHERE attributes.sla.summary.satisfiedAt IS NOT NULL
  AND attributes.custom.issueTypeTree NOT IN ('pre_order_question_non_issue.marketing_partnership_request',
'pre_order_question_non_issue.spam',
'pre_order_question_non_issue.unsubscribe')

GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12))
)
WHERE {{firstResponse_createdAt}}

But it returns Unrecognized name: kustomer.conversations at [59:28]

If it helps, here are two screenshots that show what it looks like in the UI and how I set it up in the data model.

I've been working on this for hours... please help!

flamber · April 18, 2019, 6:59pm

Hi @zma213
Currently Metabase doesn’t handle aliases, when using the Field Filter, so you need to use the full table.column name.
FROM kustomer.conversations conv should just be FROM kustomer.conversations, and then the {{firstResponse_createdAt}} has to be moved into that sub-select.
Otherwise you would have to use a regular date filter.

zma213 · April 18, 2019, 7:26pm

@flamber thanks for the quick response, but I’m still having issues. Here is a revised query:

    SELECT
agent_name,
channel,
message_count,
sla_time,
CASE WHEN channel = 'Chat' AND sla_time <= 30 THEN 'SLA Honored' WHEN channel = 'Chat' AND sla_time > 30 THEN 'SLA Breached' WHEN channel = 'Email' AND sla_time <= 15 THEN 'SLA Honored' WHEN channel = 'Email' AND sla_time > 15 THEN 'SLA Breached' WHEN channel = 'Chat and Email' AND sla_time <= 30 THEN 'SLA Honored' WHEN channel = 'Chat and Email' AND sla_time > 30 THEN 'SLA Breached' END SLA_status
FROM (
SELECT
agent_name,
channel,
message_count,
firstResponse_createdAt,
CASE WHEN channel = 'Chat' THEN bt_seconds WHEN channel = 'Email' THEN bt_minutes ELSE bt_seconds END sla_time
FROM (
SELECT
agent_name,
message_count,
inbound_message_count,
outbound_message_count,
firstResponse_createdAt,
CASE WHEN channel = '["email"]' THEN 'Email' WHEN channel = '["chat"]' THEN 'Chat' WHEN channel LIKE ('%,%') THEN 'Chat and Email' END channel,
ROUND(bt_hour*60,2) bt_minutes,
ROUND((bt_hour*60)*60,2) bt_seconds
FROM (
SELECT
  agent_name,
  attributes.custom.issueTypeTree	issueTypeTree,
  attributes.messageCount	message_count,
  attributes.messageCount -  attributes.outboundMessageCount inbound_message_count,
  attributes.outboundMessageCount outbound_message_count,
  DATE(attributes.firstResponse.createdAt) firstResponse_createdAt,
  attributes.firstResponse.time	firstResponse_time,
  ROUND(((attributes.firstResponse.time/3600000)/24) * 1,3) bt_days,
  ROUND(((attributes.firstResponse.time/3600000)) * 10,7) bt_hour,
  TO_JSON_STRING(attributes.channels) channel
FROM
  kustomer.conversations
INNER JOIN (SELECT
id,
attributes.name agent_name
FROM
kustomer.users
LEFT JOIN UNNEST(attributes.roles) roles
LEFT JOIN UNNEST(attributes.roleGroups)
GROUP BY 1,2) users
ON users.id = attributes.firstResponse.createdBy
  WHERE attributes.sla.summary.satisfiedAt IS NOT NULL
  AND attributes.custom.issueTypeTree NOT IN ('pre_order_question_non_issue.marketing_partnership_request',
'pre_order_question_non_issue.spam',
'pre_order_question_non_issue.unsubscribe')
AND {{firstResponse_createdAt}}
GROUP BY 1,2,3,4,5,6,7,8,9,10))
)

But it tells me Unrecognized name:kustomer.conversationsat [52:26]

flamber · April 18, 2019, 7:34pm

It should be something like this:

FROM
  kustomer.conversations
WHERE {{firstResponse_createdAt}}

zma213 · April 18, 2019, 7:39pm

@flamber

But that isn’t syntactically correct. And as I expected, running the query:

SELECT
agent_name,
channel,
message_count,
sla_time,
CASE WHEN channel = 'Chat' AND sla_time <= 30 THEN 'SLA Honored' WHEN channel = 'Chat' AND sla_time > 30 THEN 'SLA Breached' WHEN channel = 'Email' AND sla_time <= 15 THEN 'SLA Honored' WHEN channel = 'Email' AND sla_time > 15 THEN 'SLA Breached' WHEN channel = 'Chat and Email' AND sla_time <= 30 THEN 'SLA Honored' WHEN channel = 'Chat and Email' AND sla_time > 30 THEN 'SLA Breached' END SLA_status
FROM (
SELECT
agent_name,
channel,
message_count,
firstResponse_createdAt,
CASE WHEN channel = 'Chat' THEN bt_seconds WHEN channel = 'Email' THEN bt_minutes ELSE bt_seconds END sla_time
FROM (
SELECT
agent_name,
message_count,
inbound_message_count,
outbound_message_count,
firstResponse_createdAt,
CASE WHEN channel = '["email"]' THEN 'Email' WHEN channel = '["chat"]' THEN 'Chat' WHEN channel LIKE ('%,%') THEN 'Chat and Email' END channel,
ROUND(bt_hour*60,2) bt_minutes,
ROUND((bt_hour*60)*60,2) bt_seconds
FROM (
SELECT
  agent_name,
  attributes.custom.issueTypeTree	issueTypeTree,
  attributes.messageCount	message_count,
  attributes.messageCount -  attributes.outboundMessageCount inbound_message_count,
  attributes.outboundMessageCount outbound_message_count,
  DATE(attributes.firstResponse.createdAt) firstResponse_createdAt,
  attributes.firstResponse.time	firstResponse_time,
  ROUND(((attributes.firstResponse.time/3600000)/24) * 1,3) bt_days,
  ROUND(((attributes.firstResponse.time/3600000)) * 10,7) bt_hour,
  TO_JSON_STRING(attributes.channels) channel
FROM
  kustomer.conversations
WHERE {{firstResponse_createdAt}}
INNER JOIN (SELECT
id,
attributes.name agent_name
FROM
kustomer.users
LEFT JOIN UNNEST(attributes.roles) roles
LEFT JOIN UNNEST(attributes.roleGroups)
GROUP BY 1,2) users
ON users.id = attributes.firstResponse.createdBy
  WHERE attributes.sla.summary.satisfiedAt IS NOT NULL
  AND attributes.custom.issueTypeTree NOT IN ('pre_order_question_non_issue.marketing_partnership_request',
'pre_order_question_non_issue.spam',
'pre_order_question_non_issue.unsubscribe')
GROUP BY 1,2,3,4,5,6,7,8,9,10))
)

Produced the error Syntax error: Expected ")" but got keyword INNER at [40:1]

zma213 · April 18, 2019, 7:47pm

@flamber could it be something on the Data Model end?

flamber · April 18, 2019, 7:48pm

The filter parameter needs to be within the same SELECT as where the FROM tablename is defined, since Field Filters are converted like this (example):
{{firstResponse_createdAt}} => CAST(tablename.columnname AS DATE) BETWEEN '2019-01-01' AND '2019-04-18'
Otherwise you need to check the query being generated by looking in the browser console, Network-tab, select the POST-query, Response-tab, and look for native_form.query

flamber · April 18, 2019, 7:49pm

Why does the Data Model column name contain dots? That might cause issues, but it depends on which version of Metabase and which database you’re connecting to and version.

zma213 · April 18, 2019, 7:55pm

@flamber it contains dots because that’s how it looks if I were running it in BigQuery. If there shouldn’t be dots, what would it look like? Substitute under scores?

flamber · April 18, 2019, 7:57pm

I have no idea, I don’t use BigQuery.
Look at the response data to see the actual query - that might help figuring out what’s going on.

zma213 · April 18, 2019, 7:58pm

@flamber where do I find the response data?

zma213 · April 18, 2019, 7:58pm

@flamber right, but if I put it after the Inner Join, is that not in the same initial SELECT statement as the FROM kustomer.conversations? It just comes a little bit later?

flamber · April 18, 2019, 8:01pm

Otherwise you need to check the query being generated by looking in the browser console, Network-tab, select the POST-query, Response-tab, and look for native_form.query

flamber · April 18, 2019, 8:04pm

Or check the logs on BigQuery - if such a thing exists - to see the failed query.
Otherwise, you can use Date Filter instead of Field Filter, which just inserts a string with the date. Then you can use aliases on tables and columns. But the widget will be a little limited compared to Field Filter.