Find equal value from same field but different rows in the same table

[lotemki1]

Hi all,

I'm trying to compare values between two rows, by the same User_id but its not working.

Note: I prefer doing that in the UI and not in the script.

My table have a lot of different action and I want to compare only two of them by subsring. ( for this example it is action a and b)

Example to my table:

User_id	Action	string
1	c	bla
1	a	pass: abc
1	a	pass: add
1	a	pass: ass
1	b	pass: add
1	b	pass: ass
2	a	pass: are
2	a	pass: ers

what I want to get:

for User_Id = 1 -> 2 ( 2 equal sub string)

For User_id=2 -> 0 

The function that I did and not working:

=sum(if(aggr(mid(if(action='a', string),6,3)=mid(if(action='b', string),6,3),user_id),1,0))

* in the end I want to get the % of users that had at least one match ( the function for this user will bring 1 or +)

Can someone help me please?

Thanks in advance.

[Kushal_Chawda]

Here is your front end solution but I would not recommend as it is quite complex ans may impact the performance.  Anyway front end solution gonna be complex  due to it's complex scenario.

You can use below expression to get the % of users who have at least single match. In this case answer will be 50% because out of two only one usersid has match

=count(distinct aggr(if(
sum(aggr(if(index(Concat(total <User_id>aggr(only({<Action={'b'}>}string),string),'|'),if(Action='a',mid(string,6))),1,0),User_id,Action,string))>0,
User_id),User_id))/
Count(distinct total User_id)

Take a loot at the script solution as well

Data:
LOAD
    User_id,
    Action,
    "string",
    mid(trim("string"),6) as MatchString
FROM [lib://Web]
(html, utf8, embedded labels, table is @1);

Final:
NoConcatenate
Load *,
     if(Peek(MatchString)=MatchString and Peek(User_id)=User_id,
     if(Peek(Action)='a' and Action='b' or Peek(Action)='b' and Action='a',1,0)) as IsMatchedAction
Resident Data
Order by MatchString,User_id;

Drop Table Data;

Now you can simply use below expression to get the % of users

=Count(distinct {< IsMatchedAction={1}>}User_id)/count(distinct total User_id)