AnsweredAssumed Answered

Tableau and struggles with basic queries

Question asked by Josh Blumberg on Aug 18, 2015
Latest reply on Aug 19, 2015 by Josh Blumberg

I am new to Tableau and am struggling with some very basic queries.  Here is a basic query I am starting with:


For each course, what are the total number of assignments.  This simple metric is a good way to measure Canvas adoption on Campus (we used the same metric on the old LMS).  I have gotten this from the API before but wanted to give Tableau and Hosted Data a Chance. 


My strategy:


Join the course_dim and assignment_dim tables   (I also tried looking at assignment_fact but this caused even more issues)


This gives me something like:

SELECT "course_dim"."id" AS "id",

  "course_dim"."canvas_id" AS "canvas_id",

  "course_dim"."name" AS "name",

  "course_dim"."workflow_state" AS "workflow_state",

  "assignment_dim"."id" AS "id__assignment_dim_",

  "assignment_dim"."canvas_id" AS "canvas_id__assignment_dim_",

  "assignment_dim"."course_id" AS "course_id",

  "assignment_dim"."title" AS "title"

FROM "public"."course_dim" "course_dim"

  INNER JOIN "public"."assignment_dim" "assignment_dim" ON ("course_dim"."id" = "assignment_dim"."course_id")


The problem is that there are multiple entries for each assignment (anywhere from 1-3).  Only one of them has a valid assignment ID.  In all cases I've tested the highest number assignment ID is the valid one.  The other ones don't exist in Canvas.


The way I was trying to get a count of the number of assignments is to count the number of rows per course.  This of course makes it impossible to get an accurate count of the number of assignments since each assignment is returning multiple rows some of which are invalid. 


Can someone assist me?  This seems obvious but it is really tripping me up.