5 Replies Latest reply on Aug 20, 2018 11:38 AM by Deepak Rai

# Distinct Count of Values in a Concatenated String Using Calculated Fields

I have a data set that looks like this:

 Document Universities A University of Chicago, University of Minnesota, University of Chicago, University of Chicago, Smith College, Smith College B Colorado State University, Colorado State University, Stanford University, UMass Amherst, Stanford University, Connecticut College, Northwestern University C UCSC, UCSC D State College, Carnegie Mellon University, State College, State College

I would like to be able to count the number of unique values in the concatenated [Universities] field. Does anyone know an easy way to do this with calculated fields to get the following result?

 Document Universities Distinct Ct of Universities A University of Chicago, University of Minnesota, University of Chicago, University of Chicago, Smith College, Smith College 3 B Colorado State University, Colorado State University, Stanford University, UMass Amherst, Stanford University, Connecticut College, Northwestern University 5 C UCSC, UCSC 1 D State College, Carnegie Mellon University, State College, State College 2

Thanks!

• ###### 1. Re: Distinct Count of Values in a Concatenated String Using Calculated Fields

Hi Philipa,

Here it is. You need to Use Cartesian Join 1:1 to get this, but before that you need to create a separate List of all of your UNiversities. If You are in version above 10.3 then you can use Join calculation to join your data and List 1:1.

Thnaks

Deepak

If it Helps, Pl mark It Helpful and CORRECT to Close Thread

• ###### 2. Re: Distinct Count of Values in a Concatenated String Using Calculated Fields

Ah, thanks Deepak - unfortunately, I don't think I can do a Cartesian join because while I do have a 'list of Universities' in a separate data source, both my data sources are published data sources. Therefore, I believe I am limited to blending my data and creating calculations to de-concatenate the values to separate, if I understand how this works correctly.

• ###### 3. Re: Distinct Count of Values in a Concatenated String Using Calculated Fields

Well, In that case you need to Use extract to Do Cartesian Joins.

• ###### 4. Re: Distinct Count of Values in a Concatenated String Using Calculated Fields

Will that work even if my data is dynamic, and new universities are being added routinely to the sources?

• ###### 5. Re: Distinct Count of Values in a Concatenated String Using Calculated Fields

Yes, you just need to replace old extract with new one and add new names of universities to the List I created.

Thanks

Deepak