2

How can I check if a value in a cell of a specific column is unique in the respective column or not.

e.g. I have a column called OBJECTID. Some of the ID's are unique whereas others exist twice.

I would like to create a new column which indicates whether a value is unique or not.

Improve this question
2
  • 3
    Use an expression like: if(count("OBJECTID", "OBJECTID") = 1, 1, 0) where 1 shows it is unique, 0 it is not.
    – Joseph
    Commented Feb 21, 2018 at 13:39
  • That is actually the answer! How can I mark this as such? Thank you very much!
    – Stücke
    Commented Feb 21, 2018 at 13:53

2 Answers 2

Reset to default
3

The answer has been posted in the comments by Joseph:

Use an expression like: if(count("OBJECTID", "OBJECTID") = 1, 1, 0) where 1 shows it is unique, 0 it is not.

Improve this answer
1

you can make a virtual layer without geometry, something like

select mylayer.OBJECTID, count( mylayer.OBJECTID) 
from mylayer 
group by mylayer.OBJECTID
having count( mylayer.OBJECTID) = 1

this will produce just a table having all unique OBJECTIDs. The next step is to JOIN this table to your original data (layer properties->JOIN). The result will be an additional column with unique Ids marked with 1. If you do this without the HAVING clause at the end you will get all counts for each ID.

Improve this answer
2
  • Thank you for your response! How exactly do I have to proceed? I replaced 'mylayer' with the name of the layer I use and copy pasted the code into the field calculator expression window/field but it says that the 'expression is invalid'. Thanks!
    – Stücke
    Commented Feb 21, 2018 at 13:28
  • it is not an expression, you have to make a new virtual layer: Layer Menu->Add Layer->virtual Layer. then a window will open, with one Editarea (Query). There you have to put the query which I have posted before. The result will be an additional layer without geometry. this table you can join to your original layer.
    – eurojam
    Commented Feb 21, 2018 at 13:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.