Dave, to simplify; if you are using if
...then...else, your construct must follow these rules:
1. Your conditional test <the ... in the If(...) part of the expression> must be enclosed in parentheses ()
2. Your outcome if true <the ... in the then(...) part of the expression> must also be enclosed in parentheses ()
3. Your outcome if FALSE <the ... in the else ...) part of the expression> must also be enclosed in parentheses ()
Finally your text output must be enclosed in single quotes, so your solution might look like:
if (workorders.Complete_Date>workorders.Target_Date) then ('True') else ('False')------------------------------
Albert Valdez
albert@intelalytics.com------------------------------
Original Message:
Sent: Tue January 14, 2020 02:16 AM
From: NIGEL CAMPBELL
Subject: Issue with an IF/THEN statement
There are two ways to define a conditional expression in Cognos Analytics. If you take a look at the tool tip in the expression editors there should be simple examples of the construct.
IF ( ... ) THEN ( ... ) ELSE ( .... )
CASE WHEN ... THEN... WHEN ... THEN ... ELSE ... END
Essentially, if-then-else internally is the same thing as CASE. In fact, when you look at the SQL pushed to a database, it will be expressed as CASE.
If you were using an OLAP source, IF-THEN-ELSE may be preferred in terms of being able to push that expression down to the OLAP provider. Meanwhile CASE may force more local processing within the query engine.
In your original note you appear to indicate wanting to use the constant value TRUE which is a BOOLEAN data type.
You cannot return a BOOLEAN type as a value to a report. You would need to use alternate values such as 0 or 1.
You can use a BOOLEAN in a filter or within an expression where you are testing a value to be TRUE or FALSE.
See the expression editor and online docs re constructs if-then-else, in_range, case etc.
------------------------------
NIGEL CAMPBELL
Original Message:
Sent: Mon January 13, 2020 02:57 PM
From: Dave Chirayath
Subject: Issue with an IF/THEN statement
Hi all,
I'm stuck on trying to create a simple IF/THEN statement I am trying to use.
What I am trying to do is:
if (workorders.Complete_Date>workorders.Target_Date) then True, but it errors out.
I get the following: The expression is not valid. XQE-V5-0017 V5 syntax error found for data item 'calculation-On_Time' of query 'validateQuery', invalid token "True" found after "if (workorders.Complete_Date>workorders.Target_Date) then ".
It gets hung up on True. I've tried various other things, such as "True", 1, variablename=1, etc and I get syntax errors. I think it's a syntax that is hanging me up but I cannot figure out what.
Any help and advice is much appreciated!
Thanks,
Dave
Accruent provides leading software and services that enable organizations to shape, drive, manage and control their physical assets. For more information, visit www.accruent.com.
This message contains confidential information and is intended only for the addressee. If you are not the addressee you should not disseminate, distribute or copy this e-mail. Please notify me immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your systems. |
#CognosAnalyticswithWatson