3

I have a layer with features, which have a unique ID, called "Object_id". A second column is "Date", a third called "info" (see below).

enter image description here

What I need is to fill the fourth column "NEWID" with unique numbers for each set of features that have the same "info"-value. I already found solutions to do that in these posts: Post 1 and Post 2. But: I need to sort the "NEWID" ascending by the "Date"-column, just as shown in the table above (the lower "Date"-values need to get the lower "NEWID"-values).

The example above was filled freehand, just to clearify how it should look...

  • 1
    Well, you obviously populated your NEWID already, what have you tried to modify the expression you used? Please also edit your post so it contains the expression you used to populate your field. – Erik Sep 29 at 9:21
  • I just filled the NEWID freehand, to show an example how it should look... – Bjoern Sep 29 at 9:27
  • 1
    Still: You have solutions which solve your issue partly, what have you tried to adapt them? – Erik Sep 29 at 9:43
  • What if some info group have several different date, how would you order them ?,And what if different info group share the same date ? if none of that could happen how it is different from attributing the NEWID only by date ? – J.R Sep 29 at 10:02
  • @Erik My knowledge on Python is too limited to adapt the code, thats why I'm asking for help. – Bjoern Sep 29 at 10:03
7

This expression should do what you need:

array_find(array_foreach(array_distinct(array_sort(array_agg("Date"||'§'||"info"))),regexp_replace(@element,'([0-9]+)-+([0-9]+)-+([0-9]+§)','')),"info")+1

Note, that § sign is used as key-sign here to split the date from the id.

How it works: similar to my previous one you have linked: First you create an array by concatenating all date and info values using "Date"||'§'||"info" and sort this one ascending. This makes sure it is sorted by date. After this, we delete all duplicates, and finally remove the date and our key-concatenator § by using a regular expression. At the end we are searching the left-over ~info~ array for the current "info" and use its index as id +1.

Depending on your usecase you might want to switch the positioning of array_foreach(), array_distinct() and array_sort(), just in case someone else than op is looking this one up here.

| improve this answer | |
  • This works exactly as needed! Thank you so much, you save me a lot of work :-) – Bjoern Sep 29 at 10:41
  • @Bjoern Then please accept this answer by clicking the check mark below the question vote arrows. – til_b Oct 1 at 12:09
4

You can execute the following code in the Python Console of QGIS:

# Your layer
your_layer = iface.activeLayer()

# Unique values for your Date field stored in a list (adapts the name of your field)
idx = your_layer.fields().indexOf('date')
values = your_layer.uniqueValues(idx)
list_values = [i.toString("MM.dd.yyyy") for i in values]

# Storage in a dictionnary
# Each value of your date is associated with an id
# Use sorted method to get the dates in ascending order
your_dict = {}
id = 1 
for value in sorted(list_values):
    your_dict[value] = id
    id += 1
#print(your_dict)
# {'03.07.2020': 1, '04.07.2020': 2, '04.08.2020': 3, '05.23.2020': 4, '08.13.2020': 5}

# Update of your layer : join using the dictionary and the value of the feature's date
your_layer.startEditing()
for feat in your_layer.getFeatures():
    feat["newid"] = your_dict.get(feat["date"].toString("MM.dd.yyyy"))
    your_layer.updateFeature(feat)
    
your_layer.commitChanges()
your_layer.updateFields()   

Tell me if you need more explanations and if this is the expected result.

| improve this answer | |
  • Thank you, but your code does not exactly meet my needs.. it only sorts by date and does not in addition group by info. I updated the example-table in the question for better understanding - in my data it is possible to have multiple info-groups with the same date... – Bjoern Sep 29 at 10:40
  • Indeed, with your update, I have a better understanding of your problem – Vincent Bré Sep 29 at 11:59

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