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
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.
Quote from: BigChris on 18 Jun 2015 02:06:42 AM
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.
:D 8)
Ok thanks. Can someone please give me an example on how to do that.
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.
Ok thanks. Can someone please give me an example on how to do that.
Quote from: nac on 18 Jun 2015 10:23:29 AM
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
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...