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...

Taras
  • 32,823
  • 4
  • 66
  • 137
Bjoern
  • 283
  • 1
  • 11
  • 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 '20 at 09:21
  • I just filled the NEWID freehand, to show an example how it should look... – Bjoern Sep 29 '20 at 09:27
  • 1
    Still: You have solutions which solve your issue partly, what have you tried to adapt them? – Erik Sep 29 '20 at 09: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 '20 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 '20 at 10:03
  • @J.R There are (and in this case can not be) an info-group with different dates. If different info-groups should share the same date, the order of the NEWID is not important, but it still needs to be numbered with a unique ID for each info-group – Bjoern Sep 29 '20 at 10:06
  • What format is your data in? Can you just use SQL? ROW_NUMBER is probably all you need, along with the appropriate partition and order by clauses. – jcarlson Oct 01 '20 at 12:15

2 Answers2

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.

MrXsquared
  • 34,292
  • 21
  • 67
  • 117
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.

Vincent Bré
  • 4,090
  • 7
  • 23
  • 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 '20 at 10:40
  • Indeed, with your update, I have a better understanding of your problem – Vincent Bré Sep 29 '20 at 11:59