TransWikia.com

Coalesce field "X" if field "Y" is a duplicate

Geographic Information Systems Asked by James B on August 1, 2021

(QGIS 3.4) The image below will help explain my goal:

I’m working out how to coalesce the attributes of the field “ID” if the field “X, Y” is a duplicate.

I have highlighted some duplicate coordinates in red. My expression will create a new text field and chain all attributes in the “ID” field, delimited by a pipe (|).

For example, where I have labelled features with blue text 1,2,3, the new field will write

Special Protection Areas | Scheudled Monuments | SSSI | AONB | Special Areas of Conservtion | Scheduled monuments | Scheduled Monuments | SSSI | AONB

I will omit duplicate values later in excel.

enter image description here

Here’s what I have so far…if( "X, Y" = "X, Y" , coalesce( "ID" ),' ') but this just duplicates the “ID” field without any coalescing.

2 Answers

With credit to Vince, the correct expression to use in this case was:

concatenate( to_string( "ID" ),group_by:="X, Y",  concatenator:='|')

Correct answer by James B on August 1, 2021

In QGIS I can suggest using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...

Let's assume there is a point layer with it's corresponding attribute table, see image below.

input

With the following query, it is possible to achieve the result.

SELECT "X, Y", GROUP_CONCAT(info, ' | ') AS info_concat
FROM "points"
GROUP BY "X, Y"

The output Virtual Layer will look like as following

result

Note: Geometry is not included in the final output, otherwise extend the query with geometry parameter and to check how many points were grouped insert COUNT(), i.e.

SELECT "X, Y", GROUP_CONCAT(info, ' | ') AS info_concat, geometry, COUNT("X, Y")  AS pperloc
FROM "points"
GROUP BY "X, Y"

References:

Answered by Taras on August 1, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP