Updating multiple fields using a stand-alone look up table without Matching key either in ModelBuilder or in Python

Question

@ShitijMehta , @SMehta , @XanderBakker I want to create a tool either from Python or in ModelBuilder, but I am stuck at the last part. I have a feature class and a lookup table. Field names aliases in feature class are the same as!ARCHETYPE! Value in a Lookup table. Stand-alone table name: Tbl_HData **Feature class name: FC_HData Step 1: I want the user to select Year and Archetype fields as a selection criterion from the stand-alone table. e.g. !Year! = 2030 And !Archetype! = "Small Multiplex built >= 2007" or !Year! = 2020 And !Archetype! = "Large Multiplex built < 2007". Step 2: Select records from fields in the Feature class that matches "!Small Multiplex built >= 2007!" And "!Large Multiplex built < 2007!" (Field aliases) and value > "0", and locate rows with the !Year! 2030 and 2020. Step 3: Use values from the field !Total_Change_Elec_NatGasEmissions_Tonnes! in the stand-alone table to Multiply with field values selected in step 2 (in FC) Also if selection from step 02 has more than one fields selected then calculation be like : !Total_Change_Elec_NatGasEmissions_Tonnes! {##Field to update in FC_HData} = (!Total_Change_Elec_NatGasEmissions_Tonnes!{##Field in Tbl_HData} * !Small Multiplex built >= 2007! +!Large Multiplex built < 2007! {##Field in FC_HData} There are about 5 or 6 fields that I have to update like this in FC_HData with this logic. However, if anyone has a better logic to address this problem, please let me know. Test Data can be downloaded here. Let me if anyone got issues accessing the data. I posted this question on GIS Stack but didn't get any response for 2 days so I am trying my luck here. Looking forward to finding a solution. Thanks in advance. Hussain Problem Statement.jpg

DavidPike · Answer

OK, it's quite a lot to digest (for me at least).

But I would propose something like:

Run a search cursor on the feature class specifying a SQL query on the cursor to select only those matching records.

Append the relevant rows of each record to a list.

Open another search cursor on the table, iterate through the Archetype values from the other list (using .split(';') method to find a match.  Then perform the calculation, and append it to a new list along with the original feature class OID.

Edit - > what happens when you do have a concatenated value in the FC e.g. Small Multiplex built < 2007;Large Multiplex built < 2007

which value for Total_Change_Elec_NatGasEmissions_Tonnes from the table do you want returned, or do you want to add all the corresponding values?

HussainMalik1 · Answer

Thanks, @DavidPike, for the response.

Yes, you are right about the expression. I forgot to close (!Small Multiplex built >= 2007! + !Large Multiplex built < 2007!) In brackets.

About your first point:

I could have done that if the!ARCHETYPE! field in FC_HData wasn't concatenated. I am counting each value in the ARCHETYPE field and creating new fields with archetype names as the field name. I have been struggling with this problem for a few days now but didn't get any luck.  I tried to do it in ModelBuilder with Iterate Fields, but it doesn't select layers correctly for multiple fields.

I am primarily trying to select records for Archetype and Year in  Tbl_HDATA and locate the fields with the same name in FC_HData.  In my head, this logic seems easy to do in python, but unfortunately, my python skills are fundamental, but I think Search and update Cursor would do the magic.

About Dataset:

It's a building dataset from the city, so originally, I had unit IDs associated with each Structure IDs ( it was like 1 Structure ID had Many Unit IDs for high rise building), so based on the units Size and Year of construction, my client developed ARCHETYPEs so that we could assign energy usage (!ELECTRICAL_ENERGY_USAGE! & !NATURAL_GAS_USAGE!) value to each ARCHETYPE, apparently that's how utility providers assign energy consumption for their every account, which is why I had to create a concatenated ARCHETYPE field for each structure.

DavidPike · Answer

Can you just create a Join on ARCHETYPE to make everything simpler (one table) ? Then it's just a field calculator expression. also, in your calculation !Total_Change_Elec_NatGasEmissions_Tonnes! = (!Total_Change_Elec_NatGasEmissions_Tonnes! * !Small Multiplex built >= 2007! + !Large Multiplex built < 2007!) do you not mean: !Total_Change_Elec_NatGasEmissions_Tonnes! = !Total_Change_Elec_NatGasEmissions_Tonnes! * (!Small Multiplex built >= 2007! + !Large Multiplex built < 2007! ) ?