2 Replies Latest reply on Jan 25, 2019 1:57 PM by kate petry

    How to combine non aggregated data with aggregated data from 2 different sources

    kate petry

      I'm not sure if I'm asking the right question but here is an example of what I need.

       

      I have been asked to look at a new data set for 4 years worth of data but the first 2 years of data was provided to me already aggregated in a word document. The word document  has a list of questions with a value. For example,

      Sample data from the word document for years 1 and 2

      QuestionAnswer
      How many students took first Algebra 145
      How many students took Alegbra that had an IEP2

       

      For years 3 and 4 the data was provided to me in excel and the data hadn't been aggregated and has much more data than the word document and it contains the info necessary to answer the question. Using some fixed calculations and basic filtering I can obtain an answer of 3 students who took algebra that had an IEP. I know how to get all the answers I need for years 3 and 4 but I how do I combine the answers for years 1 and 2 with years 3 and 4? In essence how do I take data that has already been aggregated which doesn't have any row level data with data that I have to aggregate from different data sources?

       

      Sample data from the excel file for years 3 and 4

      SYSchool
      StudentGenderIEPSClass
      16-17Sunflower1234567890MTrueAlgebra
      16-17Sunflower9999999999FFalseAlgebra
      16-17Sunflower8888888888FTrueAlgebra
      16-17Sunflower7777777777FTrueAlgebra

       

      3 students took algebra that had IEPs at Sunflower for the 16-17 School Year.

       

      What I want is to be able to show how many students took algebra that had an IEP for each year but I don't know how to combine these data sets. Any suggestions? Do I blend? Join?