In this excel sheet, you can see that we have considered unique as well as duplicate value.
There are three values consisting of id 11 as shown in the excel format. But on consideration of the following formula in excel =IF(A3=A2,"Duplicate","Unique"), I get 1 out of the 3 records as unique and the rest as duplicate. I need to formulate the following formula in tableau or tableau prep. Any idea on how it can be done? Please let me know about it.
Thanks
I'm going to assume that the order doesn't matter so I'll just order by Country. In that case, create this calculated field:
Row Number
{ PARTITION [ID] : { ORDERBY [ID] ASC, [Country] ASC: ROW_NUMBER()}}See https://help.tableau.com/current/prep/en-us/prep_calculations.htm for more details on how this works. That will give you data like this:
Essentially, it will number each row sequentially, starting over at each ID, and ordering by Country. We can now count each 1 as "Unique" and all other numbers as "Duplicate".
Unique or Dupe
IF [Row Number] = 1 THEN "Unique" ELSE "Duplicate" END
End result:
See attached packaged workflow.
