Aggregate a text column

Nathan Schroeder shared this question 2 years ago
Answered

I have a text column that currently displays a unique row for each value. I would like to aggregate these, essentially with a concatenation, so that all of the text values are in one field.


Currently:


  1. 1 | A
  2. 1 | C
  3. 2 | B


Desired:


  1. 1 | A, C
  2. 2 | B


Is there a way to do this in Yellowfin?

Best Answer
photo

Yes, though slightly difficult, this is possible in Yellowfin!

To accomplish this we will create a custom function using our DB's equivalent of MySQL's group_concat or SQL Server's & PostgreSQL's string_agg. A complete guide on implementing custom functions within Yellowfin can be found here: http://wiki.yellowfin.com.au/display/USER73/Custom+Functions

In this case we will want to add a new function to the custom-functions.xml file found in the <your_yellowfin>/appserver/webapps/ROOT/WEB-INF folder:


  1. <function>
  2. <name>Group Concat, MySQL</name>
  3. <argument>
  4. <index>1</index>
  5. <name>String</name>
  6. <datatype>text</datatype>
  7. </argument>
  8. <sql>
  9. <![CDATA[
  10. GROUP_CONCAT(DISTINCT($1)) <!--Adjust this line to your DB's relevant line-->
  11. ]]>
  12. </sql>
  13. <aggregate>$1</aggregate>
  14. <database>MySQL</database> <!--Adjust this to the name of your DB -->
  15. <return>text</return>
  16. </function>

This custom function accepts a string parameter:

String - this is the text field that will be grouped and concatenated within the GROUP_CONCAT() function

Before:

8e297c508e0bf46957a2fe92fb8b4137

After:

f548c2a85b6c1265a00d1b64c1d76444

By default this expression will concatenate with a comma separating each value. To change this, you will need to research how your DB's specific function handles this and include the result in the <sql> section of the custom function.

Comments (3)

photo
2

Yes, though slightly difficult, this is possible in Yellowfin!

To accomplish this we will create a custom function using our DB's equivalent of MySQL's group_concat or SQL Server's & PostgreSQL's string_agg. A complete guide on implementing custom functions within Yellowfin can be found here: http://wiki.yellowfin.com.au/display/USER73/Custom+Functions

In this case we will want to add a new function to the custom-functions.xml file found in the <your_yellowfin>/appserver/webapps/ROOT/WEB-INF folder:


  1. <function>
  2. <name>Group Concat, MySQL</name>
  3. <argument>
  4. <index>1</index>
  5. <name>String</name>
  6. <datatype>text</datatype>
  7. </argument>
  8. <sql>
  9. <![CDATA[
  10. GROUP_CONCAT(DISTINCT($1)) <!--Adjust this line to your DB's relevant line-->
  11. ]]>
  12. </sql>
  13. <aggregate>$1</aggregate>
  14. <database>MySQL</database> <!--Adjust this to the name of your DB -->
  15. <return>text</return>
  16. </function>

This custom function accepts a string parameter:

String - this is the text field that will be grouped and concatenated within the GROUP_CONCAT() function

Before:

8e297c508e0bf46957a2fe92fb8b4137

After:

f548c2a85b6c1265a00d1b64c1d76444

By default this expression will concatenate with a comma separating each value. To change this, you will need to research how your DB's specific function handles this and include the result in the <sql> section of the custom function.

photo
1

Are there any plans on simplifying this in the future? I'm using a third party license with no access to the above. I'm using FOR XML in my view to accomplish this.

photo
1

Actually, I'm stuck. I cannot perform a FOR XML in the view because I need a date filter...

photo
1

Hi Larry,

Yea.. I do not think it will be possible to do this outside of a custom function. Due to the variety of databases and the fact that this does not work in all of them, I do not think we will ever do this as an official enhancement.

Sorry for the troubles :(

Nathan

photo
photo
1

I'd like to experiment with this. For the DB's relevant line or DB name in the function, should we refer to the DB hosting our YF app? or the target DB of our view? If it's the latter I assume we'll need separate custom functions for both MySQL and SQL sources?

photo
2

Hi Matt,

Yes if the syntax used between databases is different, then you will need different functions for each. Some functions such as AVG() are consistent so can be used without supplying a database name. The DB name in this case is the name of the data source. you are reporting on.


Good luck, and if you have trouble getting this to work, please submit a separate support ticket.


Regards,


Nathan

photo