Categories
How to group in BI Analytics
Hi,
I have a subject area with stores , each stores are in some cities, countries. How can I create an analysis that will look like below:
Name of the store | country1: list of cities comma separated; country2: list of cities comma separated; country3: list of cities comma separated
I have access also to DV. I know how to do this in SQL using GROUP by and LISTAGG , but no idea how to do this in BI Analytics.
Thank you,
Ana
Answers
-
Hi,
Your need is to concatenate text, and for that you need to use LISTAGG itself. You call it by using EVALUATE (or EVALUATE_AGGR).
That's how it is done because there isn't a "natural" (universal) was to aggregate text.
1 -
I tried to use EVALUATE_AGGR but in my request is not enough form what I tried. Can you provide the code? I need 2 levels of grouping : one by store and one by country and with EVALUATE i managed to do just one group, not 2.
0 -
It does depends on everything else going on in your analysis, in what you model is built on.
One of the first results of a random Google search:
There are a number of blog posts covering it…
0 -
Thank you,
THese are working for me, but I am trying to do 2 groups. For example, suppose that in the example from your last link, we have also Countries for each employee and I need to get smth like this:
For each department, I need a column like this : US: list of employees, DE: list of employees.
Can you help me with this? evaluate_Aggr works with just one level of grouping, not 2 levels.
I was thinking maybe is doable with dataflows ?
0 -
The example in does exactly that: it does a LISTAGG on a the names by department.
And it does definitely work as I just gave it a try in OAS 7.6:
If you add more columns, the LISTAGG will be done by each combination of all the values of all the other attribute columns. That's just standard SQL behaviour.
0 -
Hi,
Can you add inside the column with Product Category another grouping? This is what I am looking for.
Thank you,
Ana
0 -
In Oracle Analytics, you can achieve this type of output by using a combination of hierarchical columns, aggregation, and custom formula fields, similar to how you would use
GROUP BYandLISTAGGin SQL. Here’s a step-by-step guide to creating this analysis:Method 1: Using Oracle Analytics Classic Analysis
- Create a New Analysis: Open Oracle Analytics and create a new analysis based on your subject area.
- Add Columns:
- Drag the
Store Namecolumn from your subject area into the analysis. - Drag the
Countrycolumn. - Drag the
Citycolumn.
- Drag the
- Create Custom Formula:
- In Oracle Analytics, you cannot directly use SQL functions like
LISTAGG, but you can use theAggregationoptions in the column properties. - For this analysis, you can try to apply a custom aggregation to achieve a similar result to
LISTAGG. However, Oracle Analytics doesn’t directly support comma-separated lists in the visual designer, so we’ll need to use an alternative approach.
- In Oracle Analytics, you cannot directly use SQL functions like
- Pivot View:
- Add a Pivot Table View to the analysis. This will allow you to group by store and aggregate cities under each country.
- In the Rows section, drag
Store Name. - In the Columns section, drag
Country. - In the Measures section, drag
City.
- Customize Aggregation for Cities:
- Click on the
Citycolumn in the Measures section of the Pivot Table. - Choose Custom Aggregation options, but this may not create a comma-separated list. You might end up with counts or similar non-ideal aggregation.
- Click on the
Since Oracle Analytics Classic doesn’t directly support
LISTAGG, you may need to try an alternative like using Oracle Data Visualization (DV), where you can use the Data Flow feature with custom SQL.Method 2: Using Oracle Data Visualization (DV) with Data Flow and SQL
- Create a Data Flow: Open Oracle Data Visualization and create a new data flow.
- Select Your Data Source: Choose the subject area with your
Store Name,Country, andCitycolumns. - Add a Custom SQL Step:
- In the data flow, add a Custom SQL step.
- sqlCopy codeSELECT "Store Name", "Country", LISTAGG("City", ', ') WITHIN GROUP (ORDER BY "City") AS "Cities"FROM YOUR_TABLEGROUP BY "Store Name", "Country"
- Finalize the Data Flow:
- Add any further transformations if needed, then save and run the data flow.
- This will produce a dataset with
Store Name,Country, and aCitiescolumn containing a comma-separated list of cities for each country.
- Use the Dataset in an Analysis:
- Once the data flow completes, you can use the resulting dataset in a new visualization or analysis.
- You can create a Pivot Table view or a Table view to arrange the data, grouping by
Store Nameand displaying each country with its comma-separated list of cities.
Final Output
- cssCopy codeStore Name | Country1: Cities; Country2: Cities; ...
This method leverages Oracle DV’s ability to create custom SQL-based transformations, giving you control similar to
GROUP BYandLISTAGGin SQL.1 -
The above answer sounds like a piece of GenAI output, don't blindly trust it… (or not at all, it isn't the best GenAI output you could get)
Can you add inside the column with Product Category another grouping? This is what I am looking for.
What do you mean by "another grouping"? Are you saying that you want to concatenate all the "country: list of cities" you get for each store to have a single row, and 2 columns for each store?
That kind of output isn't very useful in analytics as it will be a big piece of text, you could just use a narrative view and job done.
It sounds like there are missing pieces here on what you fully need to achieve.
A dataflow isn't going to help you much as you still don't have access to SQL because you are working on a subject area.
You can definitely solve it in your database by generating that piece of text there (a SQL expression, a view, an opaque view etc.), and bring it into your RPD as an attribute for the store and then display it where you need.
1 -
indeed the answer above is AI generated, it is not working.
Yes, I need a report with all the stores, and for every store one column like this: Country1: City1, City 2 ;Country2: City1 , City 2
Report should look like this, cities and countries should be ordered!!
Store1 | Country1: City1, City 2; Country2 : City1 , City 2
Store2 | Country1: City1, City 2; Country2 : City1 , City 2
Please note that I have a data set : Store, country, city.
I would be very thankful if someone could provide a solution on this .
Thank you,
Ana
0
