counting distinct using REGEX_Countmatches....

[Raj_007]

Hi All, Thanks for your time,

I have a field with values that are seprated by the comma or , - when I try to count the distinct values in that using the REGEX_CountMatches it is not doing the DISTINCT

did I miss anything

Field1                CountColumn

xyz                         1

abc, xyz                  2

abc,xyz,MMM          3

abc,xyz,abc             2     - This should be 2 since abc is repeating

What I have is 

CountColumn=REGEX_CountMatches([Field1], ",") + 1

[Yoshiro_Fujimori]

Hi @Raj_007 ,

It seems your expression counts the number of comma, and it does not count distinct values.

I do not know if RegEx can serve for your case.

So I split the items between the separators and used Count Distinct in Summarize tool.

I hope it works.

Workflow

[Qiu]

@Raj_007 
I agree with @Yoshiro_Fujimori and use the same flow. 😁
Your regex is simply counting the occurence of character "," not the unique string.

[flying008]

Hi, @Raj_007 

Another way like @Yoshiro_Fujimori  for you.

[Raj_007]

Hi Thank you so much. This is really helpful -  noticed that ths approach works when i know the list of values - meaning it can be 1 or 2 or 3 or 4 or 5 - I dont know how many values in the form of abc,XYZ,MMM 

how did you get only column in Text TO Columns Field (FIeld1) - text to columns will break each value into each column right? how did you get only 1 column

[Raj_007]

I think i see what you have done - split to rows - when you count distinct for  each value or for each ID - how do we attach that count distinct number to the Record ID

I am getting an error message in Find and Replace Tool  - I just want to join the output of the count distinct number to the main dataset - should we use join tool or 

find and replace will work?

[Raj_007]

Find and Replace only works with strings - I was trying to replace the value of 0 which is a calculated column to be replaced the count distinct value that is coming from the summarized tool

if i change the data type to string then i can see the column name in it - but it is still showing zero only