Special Distinct Values Identifier

reynae3

Hey there!

I have been working with a field which specifies which tests a user experienced. The delimiter between each test is a comma and the delimiter between each test and version is and equal sign. 

Example (4 different users)

test1=A,test2=A,test3=B,test4=B,test5=C

test2=A,test3=B,test4=B,test5=C,test1=A

test1=B,test2=A,test3=B,test4=B,test5=C

test1=A,test2=A

Currently, counting distinct or grouping by gives me a total of 4 distinct for this data set. I need the distinct total to be 3 since the first and second user experienced the same tests and corresponding versions although the strings are in different orders.  My current method counts the first and second as different. 

Any thoughts?

Thanks for your time!

Bob_Blackey

Hi ,

This is the first thing that came to mind.  I pulled out the tests using Regex, pivot them to sort, then summarize to concatenate and then summarize to count.

Cheers,

Bob

Count Distinct.yxmd