Symbolise by maximum value field's name

[HanliePetoors]

Hi,

I'm using ArcGIS Pro 3.3.1.

I have some census data that shows how many people are employed in different industries, say Industry A, Industry B and Industry C. So each polygon has a value for Industry A, Industry B and Industry C.

How can I symbolise the census polygons to show the name of the industry that employs the most people? So if polygon 1 employs most people in Industry C, I want to symbolise it with a specific colour corresponding to Industry C.

Is this possible?

Thanks

Hanlie

[Laura]

Could you create a new field that contains data just for which industry is dominated in that polygon and then symbolize based off of that new field?

[HanliePetoors]

@Laura it's easy enough to find the maximum value but to get the field name corresponding to the maximum value is tricky.

Regards

Hanlie

[Ed_ · Accepted solution]

This might sort of help get in that direction but you might have to tweak it a little to make it work with Arcade under Symbology in Pro.

Compare values from multiple fields and return the highest and lowest values of a pop-up using Arcad...

[HanliePetoors]

@Ed_ thanks I managed to do what I wanted with a modified version of this arcade.

I give an example here for source of income, one of the groups of fields that I'm interested in.

//specify the field names to reference to
var src=[
    $feature.IncmSFrmng,
    $feature.IncmOwnBsns,
    $feature.IncmWgSlry,
    $feature.IncmOldAgPnsn,
    $feature.IncmCshRmtncs,
    $feature.IncmRtrmntIncm,
    $feature.IncmOrphnGrnt,
    $feature.IncmDsbltyGrnt,
    $feature.IncmOthr
]

var max_src=Max(src)

var max_txt = []

//Assigning the highest value
for (var v in src){
    if (src[v] == max_src){
        var kodeer = Decode(
            v,
            0, 'Subsistence Farming',
            1, 'Own Business',
            2, 'Wage / Salary',
            3, 'Old Age Pension',
            4, 'Cash Remittances',
            5, 'Retirement Income',
            6, 'Orphan Grant',
            7, 'Disability Grant',
            8, 'Other Income',
            ''
        )

        Push(max_txt, kodeer)
    }
}

return Concatenate(max_txt, TextFormatting.NewLine)

Regards

Hanlie

[Bud]

1. Can you show us some data in a screenshot of the attribute table or in an Excel export of the attribute table?
2. What kind of geodatabase? For example, Oracle 18c enterprise geodatabase.
3. Are there multiple industry fields in the existing data: INDUSTRY_A, INDUSTRY_B, INDUSTRY_C?
4. Do you want a dynamic solution (the data changes over time) or is populating a static MAX_INDUSTRY field via the Field Calculator acceptable?
5. You could post an Idea for this, asking for dynamic, out of the box functionality (no code) in the symbology pane, to see what Esri says.

[HanliePetoors]

@Bud I'm working in a SQL Server Express SDE.

Below is an example of the data with the maximum values highlighted. For OBJECTID 1 and 3 I want to return the value 'Industry Agriculture Forestry Fisheries' and for OBJECTID 2 I want to return the value 'Industry Manufacturing'. Either as a label or a value that I can use for symbology.

The data is not all that dynamic, it being census data but I have about five of these groups of fields that I would like to handle like this.

I was thinking of a SQL view or a query layer but I can't figure out how to get the field name from the maximum value. This gives me the maximum value for one of these sets of fields (source of income) but this is where I get stuck and I thought perhaps there were other ways of doing it.

select OBJECTID
	, (SELECT Max(v) 
		FROM (VALUES ([IncmSFrmng])
			, ([IncmOwnBsns])
			, ([IncmWgSlry])
			, ([IncmOldAgPnsn])
			, ([IncmCshRmtncs])
			, ([IncmRtrmntIncm])
			, ([IncmOrphnGrnt])
			, ([IncmDsbltyGrnt])
			, ([IncmOthr])							
		) AS value(v)) as [MaxIncmSrc]
from [ldr].[NAMIBIADEMOGRAPHICSPROVINCIAL2011]

Regards

Hanlie

[Bud · Accepted solution]

I normally work with Oracle, not SQL Server, but I was able to quickly mock up the SQL Server solution from this answer: Getting the column name that holds the maximum value of a row

I did it in db<>fiddle.

with temp as (
   select objectid, industry, count
   from employment_by_industry
   unpivot
   (
     count
     for industry in (agr_forest_fish, mining_quarry, mfg, electric, water_sew)
   ) u
)
select distinct t1.objectid, t2.industry
from temp t1
cross apply (
    select top 1 objectid, industry
    from temp
    where t1.objectid = objectid
order by count desc) t2

I don't know if that's the simplest solution or not.

@AlfredBaldenweck, @JoshuaBixby, and @AJR might find this interesting.

[HanliePetoors]

@Bud thanks this is a very elegant piece of SQL and a great solution.

Regards

Hanlie

[Bud · Accepted solution]

Glad it helped.

For anyone interested, here's an Oracle solution:

select objectid, max(col_name) keep (dense_rank first order by col_val desc) max_col_name
from employment_by_industry
unpivot (
  col_val
  for col_name in (
    agr_forest_fish, mining_quarry, mfg, electric, water_sew
  )
)
group by objectid

https://dbfiddle.uk/DeaRoikf

Source: https://forums.oracle.com/ords/apexds/post/idea-function-to-get-the-column-name-that-has-the-maximum...