How to String Agg a Dimension in Tableau?

Tableau does not have string_agg() function like SQL. I’d like to show list instead of asterisk when multiple values are present.

Use Case: my company works with vendors to manufacture products. One product might be manufactured by different vendors . I’d like to show vendors associated with each product. If there are multiple vendors associated with a product, use “|” between the vendors.

Solution 1: Use PREVIOUS_VALUE() function.

Vendor Name is the field that we would like to aggregate.

Vendors List Calculation:

IF FIRST() = 0
THEN MIN([Vendor Name]) 
//for the first vendor name of the product, just display the vendor name.

ELSEIF MIN([Vendor Name]) = LOOKUP(MIN([Vendor Name]), -1)
THEN PREVIOUS_VALUE("")
//if the current vendor name is the same as the last vendor name, then just repeat the previous value of this calculation (aka don't add the same value to the list again)

ELSE PREVIOUS_VALUE("") + " | " + MIN([Vendor Name])
//otherwise add the current value of vendor name to the list written out by this calculation in the previous row

END

Last Filter Calculation:

LAST() = 0 //will be used for filtering.

Tableau Workbook – create the worksheet

(highlighted cells based on product revenue, hide the vendor info because that’s company confidential information)

1. Add Vendor Name (the field that we would like to string agg) to details so that we can use it for table calculation. For Vendors List, compute using Vendor Name.

2. For measures we would like to display in the view, need to use Running Sum computing using Vendor Name.

3. Put Last function into filter, select TRUE and compute using Vendor Name.

Solution 2: Use Python Scripts.

We need to configure an analytics extension in Tableau desktop and Tableau server:

Tableau Desktop: Pass Expressions with Analytics Extensions

Tableau server: Configure Connections with Analytics Extensions

Vendors List calculation using Python Scripts in Tableau:

SCRIPT_STR("
vendors = set()
for vendor in _arg1:
    if vendor:
        vendors.add(vendor)
return ' | '.join(sorted(list(vendors)))
",
ATTR([Vendor Name])
)

For Tableau worksheet, we can use the similar steps using Solution 1.