Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to pass filter Exception date < DATE '2019-08-01' on below expression ?

Received Response
11
Views
4
Comments
Steelbird
Steelbird Rank 4 - Community Specialist
edited Aug 13, 2024 5:20PM in Oracle Analytics Cloud and Server

How to pass Exception date filter < DATE '2019-08-01' on below expression ?

EVALUATE('MAX(%4) KEEP (DENSE_RANK FIRST ORDER BY %1 DESC) OVER (PARTITION BY %2,%3,%5)' as double,"Document Details"."Exception Date","Segment1"."Segment Desc1","Item"."Item Number","Document Details"."List Price","Item"."Item category")

Thank you,

Steel.

Tagged:

Answers

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    Hi Steel,

    Have you tried putting it in a case statement, such as:

    Case when "Document Details"."Exception Date"<DATE '2019-08-01' THEN EVALUATE('MAX(%4) KEEP (DENSE_RANK FIRST ORDER BY %1 DESC) OVER (PARTITION BY %2,%3,%5)' as double,"Document Details"."Exception Date","Segment1"."Segment Desc1","Item"."Item Number","Document Details"."List Price","Item"."Item category") ELSE cast(NULL as CHAR) end

    The "ELSE cast(NULL as char)" clause should be replaced with whatever is appropriate for your data.

    Jerry

  • Steelbird
    Steelbird Rank 4 - Community Specialist

    Hi @Jerry Casey

    I have added the same case condition and noticed that it is causing null values in data, I tried to keep filter as List price is not null where it is giving error Oracle Error code: 934, message: ORA-00934: group function is not allowed here at OCI call OCIStmtExecute.

    pastedImage_1.png

    It is populating duplicate values will null in List price column.

    Is there anyway that I can restrict the case condition not to pass Null values to report.

    I have implemented in this way, I kept filter on Product number and checked the List price column without Case condition, the result it is populating as without null values,

    O/P:

    pastedImage_0.png

    List Price column is the calculated column which it this formula: EVALUATE('MAX(%4) KEEP (DENSE_RANK FIRST ORDER BY %1 DESC) OVER (PARTITION BY %2,%3,%5)' as double,"Document Details"."Exception Date","Segment1"."Segment Desc1","Item"."Item Number","Document Details"."List Price","Item"."Item category")

    Thank you,

    Steel

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    The null values are the result of the ELSE clause that I used as an example.  You can substitute whatever is appropriate for your requirements in the ELSE  clause.

    I may have misinterpreted your original question, which makes this a bad approach.

    Can you just apply the date filter to the query itself?

  • Steelbird
    Steelbird Rank 4 - Community Specialist

    Thank you @Jerry Casey, I have removed the Null and it is not populating duplicate columns.