Frontend: Grouping values in a dynamic list into ordered unique groups

[Tomm]

Hello all,

Looking for a bit of advice on how to approach a problem.

I am seeking to identify groups of polygons from a dynamic population of polygons. My two main criteria are;

1. The grouped polygons need to be relatively small in terms of sqkm. This one is solved by finding the polygons that are, in terms of sqkm, <5% of the maximum polygon in the set.

2. The grouped should be for polygons that fall within x km of each other. Current approach is to join each polygon to all other polygons and measure the distance between centroids. I then retaining those that fall under X - in addition i'm using only({< Target_Polygon = P(Polygon) >} Target_Polygon) to limit the 'nearby polygons' by filters affecting the dynamic list of polygons.

Formula: 

=AGGR(NODISTINCT

ONLY({<TARGET_POLYGON=P(POLYGON)>} 

IF(AGGR(TARGET_POLYGON_SQKM/MAX(TOTAL POLYGON_SQKM), TARGET_POLYGON)<0.005 ,TARGET_POLYGON)

)

,TARGET_POLYGON

)

Problem: Using the Qlik Sense front end I want to group clusters of polygons (dynamic list based on user selections) starting the with the biggest cluster until I reach X groups. Where a location is already grouped it should not be included in subsequent groups.

Example below; Left is current format - Right is desired output.

DYNAMIC_LOCATIONS	PRECALC_NEARBY_LOCATION	COUNT_NEARBY_LOCATION			DYNAMIC_LOCATIONS	LOCATION_GROUPS
AAA	AAA	3			AAA	GROUP_1
AAA	BBB	3			BBB	GROUP_1
AAA	CCC	3			CCC	GROUP_1
BBB	AAA	2			DDD	GROUP_2
BBB	BBB	2			EEE	GROUP_2
CCC	AAA	2
CCC	CCC	2
DDD	DDD	2
DDD	EEE	2
EEE	DDD	2
EEE	EEE	2

Next Steps - Generate X map objects that focus on each of the Location_Groups in the table below.

Thanks in advance

Tomm

[Kushal_Chawda]

@Tomm  What is the criteria of selecting Group_1 & Group_2? Example of how you arrived to Group_1 would be helpful to understand logic

[Tomm]

Grouping is done to column A for every member it relates to in column B, I.e AAA is nearby AAA, BBB and CCC so that's group 1.

BBB is also near AAA and BBB but because its already in group 1 we skip it until we hit a new area thats ungrouped. In my example thats DDD.

The order this occurs conceptually is based on how many other areas are 'nearby' forming the biggest group first and so on.

[Kushal_Chawda]

@Tomm  is the count_near_by_location is the direct field or calculated field? Also, Why not DDD & EEE in group 1?

[Tomm]

Calculate field, DDD & EEE are not 'nearby' AAA. nearby is precalculated.