Cognos Analytics

 View Only
  • 1.  Issue with an IF/THEN statement

    Posted Mon January 13, 2020 02:58 PM

    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


  • 2.  RE: Issue with an IF/THEN statement

    Posted Mon January 13, 2020 04:15 PM
    Hi Dave,
    The following should work:
    if (workorders.Complete_Date>workorders.Target_Date) then ( 1) else (0)

    My personal preference is to use case statements instead, because there are fewer parenthesis to deal with.

    case when workorders.Complete_Date>workorders.Target_Date
    then 1
    else 0
    end

    Currently(11.1.5) the output datatype of boolean is not supported as a result type of a calculation.

    // Henk

    ------------------------------
    HENK CAZEMIER
    ------------------------------



  • 3.  RE: Issue with an IF/THEN statement

    Posted Mon January 13, 2020 04:36 PM

    Thanks so much, Henk – much appreciated!

     

    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.





  • 4.  RE: Issue with an IF/THEN statement

    Posted Tue January 14, 2020 02:16 AM

    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
    ------------------------------



  • 5.  RE: Issue with an IF/THEN statement

    Posted Tue January 14, 2020 12:46 PM
    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
    ------------------------------



  • 6.  RE: Issue with an IF/THEN statement

    Posted Mon January 20, 2020 11:07 AM

    Thank you to everyone who responded to my query. I got precisely what I needed – much appreciated!

     

    Dave Chirayath

     

    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.