Thanks for your help Tushar.
However the result doesn't seem correct....
Correct me if I'm wrong. According to your table:
The expected result seems to be this:
Zone Repeated id count A 1 B 1 C 0
instead of this:
Zone Repeated id count A 2 B 1 C 0
Your table for reference:
ID Zone A001 A A001 A A001 A A001 B A002 B A003 B A004 B A005 B A004 B A001 C A002 C A003 C A004 C A002 A
According to me my results are correct. Zone A appeared 3 times for the same ID, so count should be 2. This is the logic I got from your screenshot. If this is not what you want, please tell me your expected outcome and attach a .twbx file.
Dear Zhouyi and Tushar,
Thank you for your help.
My apology, its my fault to not being clear on the question.
Actually i want the count of the distinct object id of repeated occurrences(no matter how many times repeated).
Like the screen cap below (Edited data source from Zhouyi's twbx):
Data source for reference (Edited from Zhouyi's data source):
Obj id Zone id A001 A A002 A A003 A A004 A A005 A A006 A A007 B A001 A A002 A A003 B A004 B A005 B A006 B A007 B A001 A A002 C A003 C A004 C A005 C A006 C A007 C A001 A A001 A A007 A A002 A A002 A A002 A A002 A
My apology for all the misunderstanding caused. Thank you very much.
This is the answer i am looking for. Thank you so much Deepak!