6 Replies Latest reply on Nov 26, 2018 7:21 AM by Paul Wachtler

    Combining two data sources - appending one table to the other, not join/union/etc?

    Susan Remondi

      My goal is to create a single report of classes taken for everyone from two different schools. I have two data sets, one from each school, that has username, course taken, status of course, and some other info.

       

      My problem is not creating the report - I have my Tableau report completed and working with one school's data. My problem is now trying to add in the other data set. I want the data sets to be appended lengthwise, not widthwise.

       

      Neither of the tables have any unique ID columns, and about 20% of the courses and users overlap.

       

      A sample looks like this:

       

      SchoolA.csv:

       

      UsernameCourse NameCourse StatusCourse Date
      userOneMath 101Completed1/20
      userOneMath 102In Progress2/13
      userTwoMath 101Completed1/26
      userTwoChem 101Absent6/13
      userThreeChem 101Completed9/10

       

       

      SchoolB.xls:

       

      UserCourseStatus of CourseGrade
      userFourHistory 101Completed73
      userFourMath 102Completed92
      userOneChem 101Absent0
      userFiveMath 101Absent0
      userThreeHistory 101In Progress36


      Again, I have my Tableau report set up and great with School A. I'm trying to add in School B. My goal is for Tableau to see one data set and just leave blank missing columns:

       

      Schools Info:

       

      UsernameCourse NameCourse StatusGradeCourse Date
      userOneMath 101Completednull1/20
      userOneMath 102In Progressnull2/13
      userTwoMath 101Completednull1/26
      userTwoChem 101Absentnull6/13
      userThreeChem 101Completednull9/10
      userFourHistory 101Completed73null
      userFourMath 102Completed92null
      userOneChem 101Absent0null
      userFiveMath 101Absent0null
      userThreeHistory 101In Progress36null

       

      Dropping the non-overlapping columns isn't ideal, but is something I can deal with if I need to:

       

      Schools Info:

       

      UsernameCourse NameCourse Status
      userOneMath 101Completed
      userOneMath 102In Progress
      userTwoMath 101Completed
      userTwoChem 101Absent
      userThreeChem 101Completed
      userFourHistory 101Completed
      userFourMath 102Completed
      userOneChem 101Absent
      userFiveMath 101Absent
      userThreeHistory 101In Progress

       


      I can't figure out how to do this. Everything I try - joins, unions, blends - tries to make an extended table widthwise and match columns together. I just want to be able to tell Tableau which columns are the same (e.g. username = user; course name = course; course status = status of course) and for Tableau to append one data set to the other.

       

      Because the data sets will be live and continuously changing, I can't just do this by hand by copying and pasting one into the other.

       

      I'm using Tableau Desktop, if it matters.

       

      Any and all ideas are welcome. Thank you!