1 Reply Latest reply: Dec 5, 2013 6:51 AM by odaesu RSS

    LISTAGG with Distinct Values

    953333

      Hello Everyone,

      I am trying to create a column which returns aggregated values separated by comma. I am using EVALUATE_AGGR and LISTAGG function.

       

      To elaborate, I am using Sample App Lite RPD. I created a report with Month, Brand, Product Types and Revenue columns. Product Types column has following formula:

       

      cast(EVALUATE_AGGR('LISTAGG(%1,%2) within group ( order by %1 ASC)',  "Products"."P2  Product Type",',') as char(1000))

       

      The result is:

       

      P4 Brand 

      http://slc02okm.oracle.com:7780/analytics/res/s_FusionFX/viewui/pivot/sort_plc.pnghttp://slc02okm.oracle.com:7780/analytics/res/s_FusionFX/viewui/pivot/sort_plc.png

      CAST ( EVALUATE_AGGR('LISTAGG(%1,%2) within group ( order by %1 ASC)', P2 Product Type, ',') AS CHARACTER ( 30 )) 

      http://slc02okm.oracle.com:7780/analytics/res/s_FusionFX/viewui/pivot/sort_plc.pnghttp://slc02okm.oracle.com:7780/analytics/res/s_FusionFX/viewui/pivot/sort_plc.png

      1- Revenue 

      http://slc02okm.oracle.com:7780/analytics/res/s_FusionFX/viewui/pivot/sort_asc_n.pnghttp://slc02okm.oracle.com:7780/analytics/res/s_FusionFX/viewui/pivot/sort_des_n.png

      T01 Per Name Week 

      http://slc02okm.oracle.com:7780/analytics/res/s_FusionFX/viewui/pivot/sort_asc_n.pnghttp://slc02okm.oracle.com:7780/analytics/res/s_FusionFX/viewui/pivot/sort_des_n.png

      T02 Per Name Month 

      http://slc02okm.oracle.com:7780/analytics/res/s_FusionFX/viewui/pivot/sort_asc_n.pnghttp://slc02okm.oracle.com:7780/analytics/res/s_FusionFX/viewui/pivot/sort_des_n.png

      BizTechAccessories,Accessories,Access193,3942012 Week 152012 / 04
      FunPodCamera,Camera,Camera,Camera,Ca139,5052012 Week 152012 / 04
      HomeViewInstall,Install,Install,Instal147,1542012 Week 152012 / 04

      If  you notice, the product type values are repeated in second column. Any thoughts on how to get unique values?

       

      thanks in advance