Oracle Communities

Oracle Analytics and AI

Child Item

Can we use LISTAGG function in OAC Dataflow inside Aggregate function?

I came across a couple of blogs(below) on how to use LISTAGG function in dataflows. I am not getting LISTAGG in the dropdown as displayed in the first blog and even when I try to use it in an expression like explained in the second blog, it doesn't work. Has anyone successfully implemented this in a dataflow? Please advise. Thank you.

Unlocking Formula 1 Insights: How List Aggregation Transforms Data Preparation

Functions (Data Flow) Reference

Find more posts tagged with

Function

OAC

aggregation

#Dataflow

Accepted answers

Usha Athimakula

I was able to perform a LISTAGG function using Aggregate. Had to remove all group by columns that were listed by default and leave one column that we want to group. After that I was able to see List Aggregate and List Aggregate Distinct options in the dropdown.

All comments

Usha Athimakula

Gianni Ceresa

Hi,

Just a detail: in your question you posted a link to "Functions (Data Flow) Reference". That one isn't related to OAC, it is a different independent cloud product not connected to OAC itself. They just happen to use the same wording to identify objects and provide some similar features.

The one in the link is the full ETL product named OCI Data Integration, while OAC has an embedded lightweight-ETL-like solution named data flows.

You anyway fixed your need, but in the future don't look at that "Functions (Data Flow) Reference" document when working inside OAC with data flows, it could mislead you in thinking a feature should be available.

Usha Athimakula

Hi @Gianni Ceresa,

Thank you for the insight. Appreciate it.

Now I see a new problem, After I built a Dataflow using List Aggregate and loaded data into a dataset, I went back to edit it to add new columns, all I see is an empty canvas as attached. I tried another time. I built another DF using the same List Aggregate function, closed and opened it, displays an empty canvas again. If I run it, it runs successfully, in Inspect, I see Source, Target & History but can't see anything when I edit. This is the new July release. Any thoughts? Thank You.

DF_ListAggregate_Blank1.png

DF_ListAggregate_Blank2.png

Gianni Ceresa

If your dataflow execute correctly but shows "empty" when you try to edit it, and you tried clearing your browser cache and temp files (or a different browser) and you have the same behaviour, that can be a bug and you should then raise a support request (SR) to report it. In OAC you have limited visibility on what's going on in the background, therefore a SR is the only way to get Oracle to look into it.

Usha Athimakula

I tried clearing browser cache and different browsers. Even asked other team members with write access to edit. All they could see was blank editor. I raised an SR last week. I am not sure what changed, if any fix was applied because I observed this behavior for 2 to 3 days consecutively and today it seems to be resolved before I attend a Zoom with Support to demo the issue.