counting distinct using REGEX_Countmatches....

Question

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

Raj_007 · Answer

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

ss1.PNG

ss2.PNG

Book1.xlsx

Raj_007 · Answer

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?

Capture.PNG

Capture1.PNG

Raj_007 · Answer

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