0 Replies Latest reply: Jun 3, 2014 4:14 PM by PHMA1985 RSS

    Returning Complex Row ListStagg Aggregates with different File Dates(Columns) values in the Same Row

    PHMA1985

      So this one is a bit complex and I am new to this forum so my verbage and terminology may be lacking.

       

      Basicly what I wanting to acomplish is to return the result of a CASE WHEN Query on day 1 (PVDATE) as well as returning the exact CASE WHEN Query but the data from day 2 (PVDATE2).  As all the other values will be the same I would like to basicly have a pivot table-esqe return where Query one is Column 1 and Query 2 is Column 2 and the returned value on that specific date is populated side by side.  The issue that I having is that its basicly returning two days worth of data ordered by the data date.  Below is where I stand at the moment.  I warn you now...it gets ugly.  Please note that I used Presentation Variables to set my data pull dates as our database does not populate daily.  Using PV's I am able to use a simple SQL in the Prompt to return the MAX Data Date for PVDATE and for PVDATE2 I used a limit to last two values MIN Data Date expresion.


      Column 1-Status Yesterday

      CASE WHEN "CLOV - End Product"."Rating Description" = 'Rating-Related End Products' AND EVALUATE_AGGR('LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %3 DESC)', CASE WHEN "CLOV - File Date"."File Date" = @{PVDATE2} AND("Pending Claim - Benefit Claim Status"."Claim Status Description" = 'Open' and "Pending Claim - Claim Development"."First Development Date" is null) then 'DEV' WHEN "CLOV - File Date"."File Date" = @{PVDATE} AND ("Pending Claim - Benefit Claim Status"."Claim Status Description" = 'Open' and "Pending Claim - Claim Development"."First Development Date" is not null) then 'EVD' WHEN "CLOV - File Date"."File Date" = @{PVDATE2} AND ("Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Ready for%' or "Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Incom%' or "Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Corre%') then 'RFD' WHEN "CLOV - File Date"."File Date" = @{PVDATE2} AND ("Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Compl%' or "Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Return%') then 'AWD' WHEN "CLOV - File Date"."File Date" = @{PVDATE2} AND (("Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Pend%' and "Pending Claim - Process Time"."Claim GAP Date - BDN" is null) OR ("Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Pend%' and "Pending Claim - Process Time"."Claim GAP Date - BDN" is not null)) then 'AUT' END,', ',"Pending Claim - Benefit Claim Status"."Claim Status Description") IS NOT NULL THEN (CASE WHEN "CLOV - File Date"."File Date" = @{PVDATE2} AND("Pending Claim - Benefit Claim Status"."Claim Status Description" = 'Open' and "Pending Claim - Claim Development"."First Development Date" is null) then 'DEV' WHEN "CLOV - File Date"."File Date" = @{PVDATE2} AND ("Pending Claim - Benefit Claim Status"."Claim Status Description" = 'Open' and "Pending Claim - Claim Development"."First Development Date" is not null) then 'EVD' WHEN "CLOV - File Date"."File Date" = @{PVDATE2} AND ("Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Ready for%' or "Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Incom%' or "Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Corre%') then 'RFD' WHEN "CLOV - File Date"."File Date" = @{PVDATE2} AND ("Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Compl%' or "Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Return%') then 'AWD' WHEN "CLOV - File Date"."File Date" = @{PVDATE2} AND (("Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Pend%' and "Pending Claim - Process Time"."Claim GAP Date - BDN" is null) OR ("Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Pend%' and "Pending Claim - Process Time"."Claim GAP Date - BDN" is not null)) then 'AUT' END) ELSE NULL END

       

      Column 2- Status Today

      CASE WHEN "CLOV - End Product"."Rating Description" = 'Rating-Related End Products' AND EVALUATE_AGGR('LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %3 DESC)', CASE WHEN "CLOV - File Date"."File Date" = @{PVDATE} AND("Pending Claim - Benefit Claim Status"."Claim Status Description" = 'Open' and "Pending Claim - Claim Development"."First Development Date" is null) then 'DEV' WHEN "CLOV - File Date"."File Date" = @{PVDATE} AND ("Pending Claim - Benefit Claim Status"."Claim Status Description" = 'Open' and "Pending Claim - Claim Development"."First Development Date" is not null) then 'EVD' WHEN "CLOV - File Date"."File Date" = @{PVDATE} AND ("Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Ready for%' or "Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Incom%' or "Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Corre%') then 'RFD' WHEN "CLOV - File Date"."File Date" = @{PVDATE} AND ("Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Compl%' or "Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Return%') then 'AWD' WHEN "CLOV - File Date"."File Date" = @{PVDATE} AND (("Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Pend%' and "Pending Claim - Process Time"."Claim GAP Date - BDN" is null) OR ("Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Pend%' and "Pending Claim - Process Time"."Claim GAP Date - BDN" is not null)) then 'AUT' END,', ',"Pending Claim - Benefit Claim Status"."Claim Status Description") IS NOT NULL THEN (CASE WHEN "CLOV - File Date"."File Date" = @{PVDATE} AND("Pending Claim - Benefit Claim Status"."Claim Status Description" = 'Open' and "Pending Claim - Claim Development"."First Development Date" is null) then 'DEV' WHEN "CLOV - File Date"."File Date" = @{PVDATE} AND ("Pending Claim - Benefit Claim Status"."Claim Status Description" = 'Open' and "Pending Claim - Claim Development"."First Development Date" is not null) then 'EVD' WHEN "CLOV - File Date"."File Date" = @{PVDATE} AND ("Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Ready for%' or "Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Incom%' or "Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Corre%') then 'RFD' WHEN "CLOV - File Date"."File Date" = @{PVDATE} AND ("Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Compl%' or "Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Return%') then 'AWD' WHEN "CLOV - File Date"."File Date" = @{PVDATE} AND (("Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Pend%' and "Pending Claim - Process Time"."Claim GAP Date - BDN" is null) OR ("Pending Claim - Benefit Claim Status"."Claim Status Description" like '%Pend%' and "Pending Claim - Process Time"."Claim GAP Date - BDN" is not null)) then 'AUT' END) ELSE NULL END


      I do have one thought on how to potentialy work this issue but I do not posses the coding skill or know how to acomplish such a request.

       

      I could drop the first expresion completely and change the dividing char to 'To'.  The expression returns all values encountered during the data date.  Therefore if an item was updated in a day the first data entry would be the ending data expression for the previous day.  The final data expression would be the ending data from the current day.  Therefore If I could limit the data return to the first and last values in the expression I would essentially get the "'Status Yesterday' To 'Status Today'" expression I am attempting to obtain in one column vs two for comparitive purposes.  I am sure a condition could be added to the expression itself to return the min and max data based upon timstamp but I am unsure of how to do this.

       

      Any sugestions?  I am a newbie and am self taught so please be gentle, lol. I am truely at a loss for how to solve this issue.

       

      *Please note that I do not have acsess to the back end of the database and am only able to build products from the reports and analysis side of the data stream.

       

      System:  Oracle Business Intelligence Product Version 11.1.1.6.8 (Build 130117.1603 64-bit)