3

In my data attribute:

  1. 'FID_Net' is 'unique value/subset' and 'class' is the 'value'. And in my new data sheet, the values of column 'class' is not repeating.
  2. Now I have to copy that unique value 'FID_Net' to new field 'New_FID' of only one row which value 'class' is minimum within group 'FID_Net', others row should be 0 or NULL.

As shown in below picture, how can i do in Qgis 3.x?

enter image description here

enter image description here

Kapil Dev Adhikari
  • 141
  • 8

4 Answers4

3

It seems your "FID" field is the unique ID field (equivalent to row_number or $id+1).

Then, please use this expression to update ITID_1 field:

CASE WHEN 
  "TID" = array_first(
             array_agg("TID", 
                        group_by:= "ITID",  
                        filter:= "class1"= minimum("class1" , group_by:= "ITID")))
THEN "ITID"
ELSE 0
END

This will select the first record among the minimum "class1" value for each "ITID" group.

Image below is a result of small test.

enter image description here

Kazuhito
  • 30,746
  • 5
  • 69
  • 149
  • May this will solve my problem, but when I use this expression in field calculator, qgis got stuck or hang for 20 minutes for only 2000 data records. I think processing python script will do better. – Kapil Dev Adhikari Mar 08 '19 at 15:01
  • @KapilDevAdhikari Until you get answers by python script, will you try the syntax not on the attribute table, but in the following way; (1) Open Layer Properties and select Source Fields tab. (2) Click on Pencil mark to start Edit mode, then start Field Calculator. (3) Use the above expression. --- before doing this, please close the attribute table. – Kazuhito Mar 08 '19 at 15:07
  • Problem remains the same. – Kapil Dev Adhikari Mar 08 '19 at 15:17
  • 2
    @KapilDevAdhikari OK. I give up. – Kazuhito Mar 08 '19 at 15:19
  • Can i do this task in Arcmap? – Kapil Dev Adhikari Mar 09 '19 at 10:53
  • 2
    @KapilDevAdhikari I honestly don't know. But we are always encouraged to post new question for usage of other software. – Kazuhito Mar 09 '19 at 10:56
  • Hello #Kazuhito, recently I updated my question, please review it to solve. – Kapil Dev Adhikari Dec 12 '19 at 05:08
  • Hi @KapilDevAdhikari Thanks for the update with detailed explanation. However I think the performance issue with my answer would still persist for you. Sorry. – Kazuhito Dec 12 '19 at 08:48
3

If i clearly understood what you expect this could be done using an sql query.

You go to Database / Database Manager / Database Manager then Virtual Layers / Qgis Layers you can then try the following query :

select TID,ITID, min (class1)
group by ITID

you then save the result of this query and join it (using TID) to your original table to update (or create) ITID_1

I assume TID (or is it TARGET_FID ?) is a unique ID field.

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
Snaileater
  • 5,743
  • 1
  • 15
  • 26
2

You may try running the code below in the python console in QGIS 3. There may be a more elegant way to code this task, but I tried it on a small test data set and it did work (quickly), however I am not convinced it would be faster than @Kazuhito's solution using a native field calculator expression. I will be interested to hear how it goes if you try it on your full data set. Note: I have included a progress dialog with a cancel button which should allow you to break out of the main for loop if it is taking too long. Hint: This could also be implemented in a background thread using QgsTask if necessary; or better still, put into a script for the processing toolbox since they are automatically run in a background thread where possible. There are some useful templates and tutorials for QGIS 3 processing scripts, so you would need to make an attempt at some code by yourself then post a new question if you get stuck.

Also: It seems the name of your layer is 'MT'. If that is not the case, please change the layer name string in the first line below to match yours (double check the field names in my script match yours too).

layer = QgsProject().instance().mapLayersByName('MT')[0]
fld_idx2 = layer.fields().lookupField('ITID')
fld_idx3 = layer.fields().lookupField('class1')
feats = [feat for feat in layer.getFeatures()]
itids = []
for feat in feats:
    itids.append(feat.attributes()[fld_idx2])
itid_vals = set(itids)
min_group_lists = []
v_count = len(itid_vals)
prog = QProgressDialog('Working...', 'Cancel', 0, 100)
prog.setWindowModality(Qt.WindowModal)
prog.setMinimumDuration(1)
check_value = 1
for current, val in enumerate(itid_vals):
    f = int(current + 1)
    pcnt = int(f/v_count * 100/1)
    prog.setValue(pcnt)
    if prog.wasCanceled():
        iface.messageBar().pushMessage('Info', 'Action Cancelled', level=Qgis.Info)
        break
        check_value = 0
    group = {}
    for feat in feats:
        cls1 = feat.attributes()[fld_idx3]
        if feat.attributes()[fld_idx2] == val:
            group[feat.id()] = cls1
    n_min = min(group.values())
    min_group_lists.append([k for k, v in group.items() if v == n_min])
all_min_ids = []
for l in min_group_lists:
    all_min_ids.append(l[0])
if check_value == 1:
    feats_to_update = layer.getFeatures(all_min_ids)
    fld_ID = layer.fields().lookupField('ITID_1')
    for f in feats_to_update:
        fld_val = f.attributes()[fld_idx2]
        atts = {fld_ID: fld_val}
        layer.dataProvider().changeAttributeValues({f.id(): atts})

Here is the attribute table of my test subset of data after running the code:

enter image description here

Ben W
  • 21,426
  • 3
  • 15
  • 39
0

Case when "FID_Net"=minimum("class", "FID_Net") Then "FID_Net" Else 0 End

This problem has been solved by using this expression on Qgis field calculator. But it takes about an hour to analyze 78000 field rows of data, if I know the sql syntax it would be easy to analyze in just few seconds.

Kapil Dev Adhikari
  • 141
  • 8