1 Reply Latest reply on Oct 11, 2016 3:04 PM by Patrick A Van Der Hyde

    Tableau sometimes not pulling the right data from big query

    Brian Musfelt

      For context I am attempting to make a list of "open" issues in tableau from github/zenhub data and eventually sort it by top ten longest running issues. The problem is that some of my closed issues in big query are showing up as open in tableau. . My big query is showing closed in this example but my tableau is not displaying it. Below is the SQL I wrote in big query to get the results in the table.

       

      `SELECT
      repo,
      ticket_number,
      title,
      assignee,
      state,
      pipeline,
      IF(state == "closed",IF(ABS(DATEDIFF(MAX(IF(assign_times IS NOT NULL,assign_times, created_at)), closed_at)) < 1,1,ABS(DATEDIFF(MAX(IF(assign_times IS NOT NULL, assign_times, created_at)), closed_at))),NULL) AS completion_time,
      MAX(IF(assign_times IS NOT NULL, assign_times, created_at)) AS start_time,
      MIN(created_at) AS created_at,
      MAX(closed_at) as closed_at,
      MAX(assign_times) AS assign_time,
      MAX(points) AS points,
      QUARTER(closed_at) AS quarter_closed,
      YEAR(closed_at) AS year_closed
      FROM
      [icxmedia-servers:icx_metrics.issues_and_zenhub] AS historical,
      (
      SELECT
      repository.name AS repo,
      IF(issue.number IS NOT NULL, issue.number, pull_request.number) AS ticket_number,
      FIRST(IF(issue.number IS NOT NULL, issue.title, pull_request.title)) AS title,
      IF(issue.number IS NOT NUll, issue.assignee.login, pull_request.assignee.login) AS assignee,
      pipeline.name as pipeline,
      IF(MAX(IF(IF(issue.number IS NOT NULL, issue.state, pull_request.state) == "open",0,1)) == 1, "closed","open") AS state,
      IF(issue.number IS NOT NULL, issue.created_at, pull_request.created_at) AS created_at,
      MAX(IF(issue.number IS NOT NULL, issue.closed_at, pull_request.closed_at)) AS closed_at,
      NULL AS assign_times,
      MAX(estimate.value) AS points,
      QUARTER(MAX(IF(issue.number IS NOT NULL, issue.closed_at, pull_request.closed_at))) AS quarter_closed,
      YEAR(MAX(IF(issue.number IS NOT NULL, issue.closed_at, pull_request.closed_at))) AS year_closed
      FROM
      [icxmedia-servers:icx_metrics.gh_zh_data_production]
      WHERE
      issue.number IS NOT NULL
      OR pull_request.number IS NOT NULL
      GROUP BY
      repo,
      pipeline,
      ticket_number,
      created_at,
      assignee) AS prod
      GROUP BY
      repo,
      ticket_number,
      title,
      assignee,
      pipeline,
      state,
      quarter_closed,
      year_closed
      ORDER BY
      ticket_number DESC`

       

      Screen Shot 2016-09-16 at 11.56.06 AM.png

      Screen Shot 2016-09-16 at 11.56.40 AM.png