Oracle Analytics and AI

Combining multiple rows

I would like to combine the values from multiple rows into one column.

Instead of getting

Name	Address
John	Address1
	Address2
	Address3

I would prefer to have

John addresss1, address2, address3

Note I do not have administrator privileges so this would have to be done within a custom column formula, I imagine.

Build is 11.1.1.9.5.

Find more posts tagged with

Accepted answers

All comments

Joel Acha

Hi @Wolfrm

If you have access to call Oracle Analytic functions via EVALUATE, you could try using LISTAGG

Wolfrm

Thanks, Joel. I tried using listagg but couldn't for the life of me figure out how to correctly call that function. I do have access to evaluate and evaluate_aggr.

Joel Acha

Something like this should do the trick:

EVALUATE_AGGR('LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %3 DESC)',OBIEETableName.OBIEEColumnName,';',OBIEETableName.OBIEEColumnName)

Wolfrm

I tried using that but it did not work and on closer inspection, it seems I don't actually have access: Message returned from OBIS. EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE.

I had incorrectly assumed I had access to EVALUATE because the function was available in the list of functions in the custom column screen.

Is there any other way to accomplish this, without using LISTAGG?

[Deleted User]

Not really because OBI relies on the data source to interpret these windowing functions!

Wolfrm

That is unfortunate. An alternative solution would then be to find a way to use line breaks in tables, since I could then massage the table layout to produce what I need without using LISTAGG to combine rows. But for this I would need line breaks in the table view. Any ideas? Note that I asked this in my other thread and accepted narrative view as answer to using line breaks, but that was before I run into this next issue, in this thread.

[Deleted User]

Even if it means writing an opaque view...how about using one of those to call the LISTAGG or directly do the view in the DB?

Wolfrm

Thanks, Christian. My access is really limited - I cannot create opaque views nor do I have direct access to the database.

[Deleted User]

Okeeeey so then you're basically in a situation where you can't achieve this with normal config.