IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
4 replies Latest Post - ‏2013-06-13T16:50:25Z by Prinny
15496 Posts

Pinned topic Query Assistance

‏2013-04-05T19:35:32Z |
Relatively new to cognos and am trying to create a report but am stumped at the moment. Here is the situation:


Data Items:

Employee Employee # Survey Name Question Name Answer
Jim 123 New Survey ......... ...........
Bob 456 New Survey blah blah blah blah
Bob 456 Old Survey ........ ............
Sue 789 Old Survey ......... .............

Scenario: Originally the survey was named Old Survey and some employees completed that survey, a new survey was then created and some employees completed that survey as well.

In the example I want Jim's answers from new survey as that is the only survey he completed. Bob's answers from New survey even though he completed both surveys (as the new survey contains the most current responses) and Sues answers from Old Survey as she has never completed a new survey.

Any thoughts on a query that would basically say

if new survey exists and old survey does not exist use new survey
if old survey exists and new survey does not exist use old survey
if both surveys exists use new survey???

This report is being created through a hosted solution so I can not make a direct call to SQL. Can anyone offer any help?
Updated on 2013-04-06T15:48:14Z at 2013-04-06T15:48:14Z by SystemAdmin
  • SystemAdmin
    15496 Posts

    Re: Query Assistance

    ‏2013-04-06T14:24:27Z  in response to SystemAdmin
    I don't know what model is yours. If there is any date field then you can make it like maximum(date for employee), then it will fetch latest record for each employee.

  • Mir Mohammed Ali
    Mir Mohammed Ali
    1 Post

    Re: Query Assistance

    ‏2013-06-03T16:28:56Z  in response to SystemAdmin


         How about creating a data item in the existing query (lets call this existing query  'Base Query' for the sake of discussion) called 'Survey Type Flag'.  The logic for this data item would be:  if(Survey Name='New Survey' then 1, else 0).

      Next, pull another query based on top of the 'Base Query' and grab just the Employee and create the following expression on the 'Survey Type Flag' : Maximum( Survey Type Flag for Employee).  Lets name this data item 'Recent Survey Flag'.  Basically, this query (lets name it 'Employees by recent Survey Flag' would give us the Employee and the most recent survey flag.

    Finally, we'll pull in another query where we inner join 'Base Query' and 'Employees by recent Survey Flag' on the Employee field and 'Base Query'.Survey Type Flag='Employees by recent Survey Flag'.Recent Survey Flag

    Hope that helps.



  • Prinny
    1 Post

    Re: Query Assistance

    ‏2013-06-13T16:50:25Z  in response to SystemAdmin

    Split the query into 2, 1 with new surveys and 1 with old surveys, do a left join with old being on the left. Then use a if statement if new is null then old else new.