27 Aug 2019

QGIS 3: Layout expression to get all features of a layer within the current map view

In a map layout template you can insert an expression which gets all feature's names of a certain layer, which are contained in the current layout's view/extent. For wxample you could concatenate all feature's names contained in the current map view like so:

aggregate(layer:='Corridors_86ef2ea9_d1a1_4d9f_9735_7b7b2fb54cb2', 
aggregate:='concatenate', 
expression:="Name", filter:=within($geometry, map_get( item_variables('Main Map'), 
'map_extent')), concatenator:=', ')

13 Aug 2019

Spatial Join in QGIS 3.x with Field Calculator alone!!

For example I want to join Polygon attribute values to a point layer. So, edit mode in target layer, check "Update Field" and put field you want to join to. Then use this in expression builder, where is the join layer, aggregate is the function to be used on attribute values that intersect the target geometry, and is the join attribute, from where the values come!

aggregate(layer:='BIKFFH_Karwendel BIKFFH_PL', aggregate:='sum', expression:="LNUMMER", filter:=intersects($geometry,geometry(@parent) )) 

20 Feb 2019

Return Excel Row Indices of non-empty Cells in Column

Column with Data in A1:Â20

Formula in Cell B1

=WENNFEHLER(AGGREGAT(15;6;ZEILE($A$1:$A$20)*N(LÄNGE($A$1:$A$20)>0);SUMMENPRODUKT(N(LÄNGE(Datenablage!$A$1:$A$20)=0))+ZEILE());"")

SUMMENPRODUKT(N(LÄNGE(Datenablage!$A$1:$A$20)=0)) is needed because each LEN=0 will be a smallest value, so if you have 3 cells with LEN=0 the ksmallest with k=4 will be the first non empty value..

..for details on aggregate check: https://www.youtube.com/watch?v=He3dblboncw