For a specified measure, Oracle's LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column. How do we implement the LISTAGG() function in Incorta?
We can implement this using Spark SQL in a Materialized view using the collect_list() function and a group by.
For example, the DESCRIPTION below is a concatenation of all the child values delimited by a semi colon. Once we have this MV we can create a dashboard on top and filter on the concatenated values using the CONTAINS operator.
select distinct CHG.CUSTOMER_TRX_ID,
concat_ws('; ', collect_list(TRX.DESCRIPTION) OVER (PARTITION BY TRX.CUSTOMER_TRX_ID ORDER BY TRX.DESCRIPTION ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) as DESCRIPTION
from EBS_AR.RA_CUSTOMER_TRX_ALL CHG
LEFT OUTER JOIN
EBS_AR.RA_CUSTOMER_TRX_LINES_ALL TRX
ON CHG.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
where 1=1;
Sample Output:
CUSTOMER_TRX_ID, DESCRIPTION
100, 'sample1; sample2; sample3'
.png "Employee"){kind=icon}
