Print Page - IF and Else Statement with DATE = MAXIMUM(DATE) for ID

Title: IF and Else Statement with DATE = MAXIMUM(DATE) for ID
Post by: gosoccer on 20 Mar 2017 04:09:15 PM

Hi guys,
I'm stuck big time and I have to have this done by tomorrow. I have to come up with the following in FM Model Subject Q,

IF ([LTDATE IS NULL) THEN
(LTDATE = MAXIMUM[LDDATE] for ID)
ELSE
(SFDATE= MAXIMUM[SFDATE] for ID)

I'm getting a parsing error.

Please help if you can..

Title: Re: IF and Else Statement with DATE = MAXIMUM(DATE) for ID
Post by: gosoccer on 20 Mar 2017 05:36:03 PM

Ok, I created two max columns using the calculations as maxltdate and maxsfdate.
and then I have the following if statement but I'm getting Highlighted expression has Wrong Type

if (XXXX.XXXX.[LTDATE] IS NULL) then
(XXXX.XXXXX.[MAXLTDATE])
else (XXXX.XXXX.[MAXSFDATE])

I Thought if I create the column under the subject query calculation without using the FOR Clause, it would help but
I think there is a data mismatch of some sort.

Any help, is greatly appreciate it.

Title: Re: IF and Else Statement with DATE = MAXIMUM(DATE) for ID
Post by: tjohnson3050 on 20 Mar 2017 08:40:26 PM

The syntax for maximum is maximum(XX for YY)

IF ([LTDATE IS NULL) THEN
(LTDATE = MAXIMUM([LDDATE] for ID))
ELSE
(SFDATE= MAXIMUM([SFDATE] for ID))

Title: Re: IF and Else Statement with DATE = MAXIMUM(DATE) for ID in Framework Manager
Post by: gosoccer on 22 Mar 2017 05:58:29 AM

Hi there, Thanks of the reply,

Well, let me share this with you and let me know what you think since I'm so soo stuck in Framework Manager.

This query contains an error and can not be executed.

It is recommended that you view the query feedback on the "Query Information" tab.

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0358 Line 129: Syntax error near "=".

Here is the Filter Statement under the Framework Model subject query,
case
when XXXX.[REC_TYPE_CD] = 1
then (XXXX.[Max Updated] = maximum(XXXX.[Max Updated] for XXXX.[Record ID 1]))
when XXXX.[REC_TYPE_CD] = 4
then (XXXX.[Max Updated] = maximum(XXXX.[Max Updated] for XXXX.[Record ID 4]) )
end

and [Max Updated] is a Expression under the Query Items and Calculation of the Subject Query listing of items with the following,

case
when (xxxx.[DT_TS] IS NULL) then xxxx.[Last Date]
else xxxx.[DT_TS]
end

This error is very annoying.

I have to perhaps shift to using just OR and AND statements but not sure how to establish that. I think using the FOR as a par :) :)t of the above is causing this issue.

Title: Re: IF and Else Statement with DATE = MAXIMUM(DATE) for ID
Post by: bus_pass_man on 22 Mar 2017 01:02:31 PM

If you got rid of the XXXX.[Max Updated] = bits what happens?

Title: Re: IF and Else Statement with DATE = MAXIMUM(DATE) for ID
Post by: gosoccer on 22 Mar 2017 01:30:35 PM

Thank you for replying. Since I was getting error no matter what i tried, I end up using the

(Statement) AND (Statement)
OR
(Statement) AND (Statement)
OR
(Statement) AND (Statement)

it's working absolutely great. We validated all the data and it was successful.

::) ::)

Title: Re: IF and Else Statement with DATE = MAXIMUM(DATE) for ID
Post by: bus_pass_man on 23 Mar 2017 08:29:34 AM

In general you want filter expressions to resolve to a boolean. Your expression wasn't doing that. It was more of a calculation type expression.

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Framework Manager => Topic started by: gosoccer on 20 Mar 2017 04:09:15 PM