Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ksmccann
New Contributor III

Finding the latest response to a survey question

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.

Community Browser