0 Replies Latest reply: Sep 17, 2012 5:10 PM by Kevin McCann RSS

    Finding the latest response to a survey question

    Kevin McCann

      Hi All

          I have an issue where I am trying to identify the last response to a survey question in a given period of time. I am pulling the data from 2 sources, a SQL query gets me all of the resposnes and the period in which they fell in.  I have a second source which is an Excel spreadsheet that breaks the period into 2 ranges of dates, for the first half of the period and the second half of the period. What I need to show is the last response within the first half of the period and the last response within the second half of the period.

       

          I can successfully pull the last response within a period since that is on my original SQL table but I don't seem to be able to hit on the correct solution with the XLS data that provides a range of dates to group the responses by.

       

           The following code worked for the last response across the entire period

       

      LOAD StoreID,

      StoreID AS RptdStoreID ,

      upper(PrincipalDescription) as PrincipalDescription,
         SurveyTypeDescription,

      Questionid ,

      Questiontext,

      ResponseTypeCode,
      ResponseID ,

      ResponseDescription ,

      SurveyYesNo,

      SurveyQuantity,

      SurveyPrice,

      SurveyResponseText,

      date(LastChange) as SurveyDate ,

      MinResponseId,

      Period,

      PeriodLastResponse,

      StoreID & '-' & SurveyTypeDescription & '-' & Questionid as StoreQuestionCount

      //,FirstValue(ResponseDescription, LastChange) as FirstResponse  

      //FirstSortedValue(ResponseDescription, LastChange, -1) as LastResponse  // Last value


      FROM Survey.qvd (qvd);

       

       

       

       

      left join (Survey)


      Load  StoreID,

      SurveyTypeDescription,

      Questionid ,

      Period,

      FirstSortedValue(ResponseDescription, LastChange) as FirstResponse  ,

      FirstSortedValue(ResponseDescription, -LastChange) as LastResponse,  // Last value0

      date(FirstSortedValue(LastChange, LastChange)) as FirstResponseDate  ,

      date(FirstSortedValue(LastChange, -LastChange)) as LastResponseDate  

      FROM Survey.qvd (qvd)

      group by  StoreID, SurveyTypeDescription, Questionid,Period;

       

       

      Now my issue is getting the second data source in as part of the Grouping fields. The 2nd data source looks as follows

       

      CYCLESTART DATEEND DATECall Numbercall start datecall end date
      2012011/11/2811/11/14
      2012011/11/2821/151/28
      2012021/292/2511/292/11
      2012021/292/2522/122/25
      2012032/263/2412/263/10
      2012032/263/2423/113/24

       

      I have tried playing with IntervalMatch and doing left joins on this table but none of those seem to be getting the correct answer.

       

      If I did this in SQL I would join the 2 tables, join on the period and where survey date between callstartdate and callenddate and then do a grouping by Cycle and CallNumber for a max on SurveyDate. 

       

      Appreciate any help on this.