finding count of the unique rows values in a column

[Vignesh Kannan]

hi,

i have columns as shown below:

 

I want to find out : if column 1="A" then find column 2 is repeated how many times like below :Under A filter AB is repeated 2 times

 

 

 

column 1

column 2

calculation column

 

 

A

AB

2

 

 

B

AB

NA

 

 

A

AB

2

 

 

B

BC

NA

 

 

A

BC

3

 

 

B

BC

NA

 

 

A

BC

3

 

 

B

BC

NA

 

 

A

BC

3

[Shashank Gurnalkar]

Use the following expression:

case

when ([column 1]="A") and ([column 2]="AB") then Count([column 2]) OVER ([column 1])

when ([column 1]="A") and ([column 2]="BC") then Count([column 2]) OVER ([column 1])

endIt will give results the same as your shown calculated column.

[Vignesh Kannan]

hi thanks for the solution,

 

i have one more clarification if i have only one column as shown below how to find unique values and repated values.

 

 

 

column 1	calculation column
AB	First Row
AB	Duplicates
AB	Duplicates
BC	First Row
BC	Duplicates
BC	Duplicates
BC	Duplicates
BC	Duplicates
BC	Duplicates

 

 

 

i tried to use the below  function  but doesn't work:

 

If(Rank(Baserowid(),"asc",[myGroup1])=1,"First row of group","Duplicate")

 

 

 

Link reference:https://support.tibco.com/s/article/Tibco-KnowledgeArticle-Article-42284

[Shashank Gurnalkar]

You need to add your column in it. Use this expression. It's working for me.

 

If(Rank(Baserowid(),"asc",[column 1])=1,"First row of group","Duplicate")