Query needed

[nimishabrahamc]

Hi,

I am quite new to Cognos. I wanted to write a query in the Expression Definition.

I have 4 columns:

Type, Initial , Date, Amount

The condition is:

If 'Type' is X or Y

and if there is an 'Amount' one day prior to the 'Date'

then 'Initial' should be 'NO' Else 'YES'.

Kindly provide me the query for this.

Thanks,
acn

[BigChris]

Someone will probably contradict me, but I don't think you can do that in a single query expression. I think you 'd need to write two  queries, one in which you calculate the previous day. Then create an outer join using the [previous day] and [date] fields.

[Lynn]

Someone will probably contradict me, but I don't think you can do that in a single query expression. I think you 'd need to write two  queries, one in which you calculate the previous day. Then create an outer join using the [previous day] and [date] fields.

No contradiction from me. I don't see another way to solve that problem.

[BigChris]

 :D 8)

[nimishabrahamc]

Ok thanks. Can someone please give me an example on how to do that.

[CognosPaul]

Is this a relational or dimensional data source? If it's dimensional I can totally contradict BigChris.

Easiest way with two queries, create a query that has date and measure filtered by type in x,y. Create another query that has _add_days(date ,1) and measure. Join those with an outer join.

[nimishabrahamc]

Ok thanks. Can someone please give me an example on how to do that.

[Lynn]

Ok thanks. Can someone please give me an example on how to do that.

Joining queries is covered in standard documentation.

http://www-01.ibm.com/support/knowledgecenter/SSEP7J_8.4.0/com.ibm.swg.im.cognos.ug_cr_rptstd.8.4.0.doc/ug_cr_rptstd_id9396cr_rptstd_wrkdat_create_join_relatio.html%23cr_rptstd_wrkdat_create_join_relationship_rel

[BigChris]

As CognosPaul says, it's easy with a dimensional source...is that what you're using, or is it relational?

Assuming it's relational, you need two queries and a join between them. In your first query, bring in the fields that you need. Then create a copy of that query and create an additional field in it with an expression of something like _add_days([DateField],-1) and call it PreviousDate.

Then join your two queries with an outer join, based on [DateField] from the first query and [PreviousDate] from the second. Check your cardinality so that you get the right output. The link that Lynn has posted should give you the instructions for doing that. Once you've done that you should be able to create the expression that you originally asked for...