Inline variable with "Table of Values" option and its handling

Jump to solution
01-31-2021 10:35 PM
Labels (3)
New Contributor III

Hello Community, 

@DavidPike , @ShitijMehta , @XanderBakker , @Robert_LeClair , @Philip-Wilson , @JoshuaBixby , @RobertScheitlin__GISP , @JoeBorgione , @OwenGeo , @curtvprice 

So here is a thing:

I am building a tool to calculate Green House Gas emission from the city buildings. And there are 25 to 30 Variables that are involved in the calculations and there are around 90 new calculated fields that will be added in the process to incorporate these variables.

There are 2 main fields present in the data set that will be involved in building scenarios !Year! and !ARCHETYPE! (See Image Year and Archetype fields in dataset.jpeg)



So, I have created a Variable in Model builder with “Table of Values “option, this option gives me a chance to build scenarios using multiple values for same variable, as shown in image (Table of Values Variable.jpeg). Year and Archetype are the fields as well as variable.

My first Question:

How do I select data which matches my %Year% and %Archetype% variable values in dataset, since !Year! and !ARCHETYPE! fields are present in the dataset.



My second Question:

How do I separate OR use all these variables with their names (See image Collect Values from Variable.jpeg) so that I could use them in calculations later? If I use Collect Values tool, I need to split these values. I am not so good with Python so If I need to call a function to split these values, I will need help with the function.



I can share data if requires.

I would really appreciate the help and thanks in advance.


0 Kudos
1 Solution

Accepted Solutions
Esri Esteemed Contributor

Hi @HussainMalik1 

I suggested creating a table with the 5200 records for all archetype, year and variable combinations. Like this:


(I used this snippet to create the content:)

import random

# 26(archetype)*4(years)*50(variables)
for x in range(26):
    archetype = "archetype {}".format(x + 1)
    for y in range(4):
        year = 2020 + y * 10
        for z in range(50):
            variable = "variable {}".format(z + 1)
            value = random.random() * 25
            print("{}\t{}\t{}\t{}".format(archetype, year, variable, value))


I converted the data to an ArcGIS table in a FGDB and ow you can read the values either into a dictionary and query directly on archetype and year (if you are planning to use all the data) or simply load the variables related to a specified archetype and year using a query definition while reading the table.

See the code below (including both example):

import arcpy

def main():

    # table location and field names
    tbl = r'D:\GeoNet\EnergyCalc\ArchetypeYear\Default.gdb\ArchetypeYearData'
    fld_arche = 'Archetype'
    fld_year = 'Year'
    fld_var = 'Variable'
    fld_val = 'Value'

    # input values to query
    archetype = 'archetype 19'
    year = 2030

    # get values using a dictionary
    dct = ReadDataIntoDict(tbl, fld_arche, fld_year, fld_var, fld_val)
    dct_res1 = GetDictValuesForArchetypeAndYear(dct, archetype, year)

    # get values using the table and a query definition
    dct_res2 = GetTblValuesForArchetypeAndYear(tbl, fld_arche, fld_year, fld_var, fld_val, archetype, year)

def ReadDataIntoDict(tbl, fld_arche, fld_year, fld_var, fld_val):
    dct = {}
    with arcpy.da.SearchCursor(tbl, [fld_arche, fld_year, fld_var, fld_val]) as curs:
        for row in curs:
            archetype = row[0]
            year = row[1]
            variable = row[2]
            value = row[3]
            if archetype in dct:
                dct_arche = dct[archetype]
                if year in dct_arche:
                    dct_year = dct_arche[year]
                    dct_year[variable] = value
                    dct_arche[year] = dct_year
                    dct[archetype] = dct_arche
                    dct_arche[year] = {variable: value}
                    dct[archetype] = dct_arche
                dct[archetype] = {year: {variable: value}}
    return dct

def GetDictValuesForArchetypeAndYear(dct, archetype, year):
    return dct[archetype][year]

def GetTblValuesForArchetypeAndYear(tbl, fld_arche, fld_year, fld_var, fld_val, archetype, year):
    dct = {}
    query = "{} = '{}' AND {} = {}".format(fld_arche, archetype, fld_year, year)
    with arcpy.da.SearchCursor(tbl, [fld_var, fld_val], query) as curs:
        for row in curs:
            dct[row[0]] = row[1]
    return dct

if __name__ == '__main__':


In this example, specifying archetype "archetype 19" and year 2030 used the following query definition:

Archetype = 'archetype 19' AND Year = 2030


...and returned a dictionary with all the variables for this query (providing access to all the 50 variables):

{'variable 1': 15.5244401870236, 'variable 2': 23.3657321633143, 'variable 3': 15.1579971665677, 'variable 4': 22.4358991530334, 'variable 5': 4.29857939816623, 'variable 6': 9.85983758111721, 'variable 7': 18.5286923152794, 'variable 8': 5.79755699855013, 'variable 9': 22.7302585515292, 'variable 10': 20.7952926242024, 'variable 11': 3.15393340209763, 'variable 12': 6.55497889635969, 'variable 13': 23.534915278783, 'variable 14': 0.320347367664924, 'variable 15': 7.48007771767636, 'variable 16': 19.7211779286983, 'variable 17': 5.28625570863067, 'variable 18': 7.73552043487549, 'variable 19': 17.2153002750518, 'variable 20': 6.89264095850893, 'variable 21': 2.36278203597135, 'variable 22': 7.46633514134018, 'variable 23': 3.48576873823912, 'variable 24': 4.14460495556789, 'variable 25': 21.7298557989618, 'variable 26': 12.3477762544827, 'variable 27': 5.06567178346359, 'variable 28': 23.3938881028709, 'variable 29': 9.22447098083539, 'variable 30': 20.6246227233889, 'variable 31': 9.2655784448017, 'variable 32': 16.7829187579809, 'variable 33': 5.41408450739008, 'variable 34': 10.4562086747899, 'variable 35': 12.3017493699073, 'variable 36': 4.87453196521707, 'variable 37': 21.2273082111302, 'variable 38': 9.83037462145755, 'variable 39': 6.71378949875001, 'variable 40': 17.1075560942317, 'variable 41': 13.9113667208803, 'variable 42': 21.2160384165932, 'variable 43': 10.144770899068, 'variable 44': 7.1533553156325, 'variable 45': 5.7455521841622, 'variable 46': 9.92663148960041, 'variable 47': 3.13525525773836, 'variable 48': 12.5155910723043, 'variable 49': 20.245329425554, 'variable 50': 0.227518270529811}


This can then be used to do the calculations.


When I looked at your features, I noticed that you have 4 copies of the same geometry (one for each year( and you have repetitions of multiple archetypes for a large part of the data. I don't quite see how you are planning to connect this data to the features you have. 

Can you explain more about this? What is a scenario supposed to return?

View solution in original post

18 Replies
Esri Esteemed Contributor

Hi @HussainMalik1 ,


I think I would probably use a matrix (table) to store the values for each Archetype and Year combination. This makes it easier to edit in case values change over time. 

New Contributor III

Hi @XanderBakker 

Can you suggest any workflow or any logic around this. 



0 Kudos
Esri Esteemed Contributor

Hi @HussainMalik1 ,


Can you elaborate a bit more on the calculations that you are planning to carry out? You mention 25-30 variables and are planning to create about 90 calculated fields. Are these fields all required or are these intermediate results. Maybe it is not necessary to have all these new fields in the end result. 

As far as I understand you are planning to create a model. Model Builder is a very powerful tool to build a model and automatize processes. However, when things get very complicated it might be better to use scripts to solve the problem. This provided more flexibility to solve the problem but will require a certain level of Python knowledge. 

I am happy to help you further, but my time might be limited. So, depending on the complexity of what you are trying to accomplish, I might be able to help. It will be important to share more details so the scope and complexity can be determined correctly. 

0 Kudos
MVP Esteemed Contributor

This table of values input capability looks pretty slick, I am not familiar with it.

It seems to me a workflow to extract all these variables into model builder variables would not be ideal, you would probably have better luck doing calculations with a table, using a Python function embedded inside a the Calculate Value tool. This python function would do your calculations using a an update cursor (arcpy.da.UpdateCursor). I think this approach would be much better for what you are trying to do.

A little bit of Python can make ModelBuilder go a lot farther. However, as you add complexity you may be forced to move toward Python scripting.

0 Kudos
New Contributor III

Thanks @curtvprice for getting back to me! 

I totally agree with you regarding using Python, and that's what i am looking for here, some sort of a function or code that i can call to Calculate Value from the variable if you open the attached image "Table of Values Variable.jpeg" you will see the multiple variables in it.

All I need at this point is using Year and Archetype in the variable to select record in attribute table since these Year and Archetype fields are also present in attribute table (Attached image Year and Archetype fields in dataset.jpeg) . 

0 Kudos
New Contributor III

Thanks @XanderBakker 

Objective of the project: 

"Build scenarios for different years between 2020 to 2050 to reduce the energy consumption from the city buildings."

Purpose of the Project

I am calculating the change in electricity and natural gas demand for all the buildings present in the city from year 2020 to 2050, the energy consumption is further divided into smaller components based on the type of energy these components are using whether its Electricity or Natural gas such as domestics appliances (refrigerator, freezers and oven etc.), Electronic plugs loads , lighting and Electric domestics hot water equipment (NOTE: there are more components involved in this project). The cumulative energy consumption data is provided by utility companies for each archetype. 

Now  the idea is to break down the cumulative energy value (both electric and Natural gas) and assign some percentage of the value to these components  mentioned above. I am using Inline variable method to create these variables where user can put the percentage value for each components.

Here is a link where you can access the excel model fields that would show the breakdown of energy into different household components, that might help you understand the complexity of the model. (Please let me know if it requires more explanation from my end).

 Little bit about dataset: 

Dataset contains one feature class that has Electricity_ENERGY_USAGE and NATURAL_GAS_USAGE values in 2 separate fields along with the ARCHETYPE  and Year fields. Year and Archetype field are the main fields that will be used to build scenarios e.g: user might want to do following : if in Year 2030 Electric consumption for Domestic Appliance is 30% of Electricity_ENERGY_USAGE but for 2040 its reduced down to 25% of Electricity_ENERGY_USAGE (in this scenario Electric % change in Domestic Appliance   is one of the variables for my calculation.  And ultimately the result from this calculation will be saved in the new field i.e:  Change in Domestic Appliance Electricity   Attribute table image is attached in this post for your review

About my Logic in ModelBuilder 

Selecting Data by Year and Archetype: 

I am trying to create inline variable with "Table of Values" that would give me an option to incorporate Year and Archetype along with other variables in it. Since Year and Archetype are fields already present in the dataset i am hoping to call these fields from the variable and select all the record that matched the criteria. 

Assigning multiple values of variable to the selected data: 

Assuming I am able to find a way to select records from variable Year and Archetype, then I would want to assign other variable values such as %change in Domestic Appliances to those selected record.

 As far as Calculation goes i am only calculating %change for each  component of energy consumption, which is not complicating. 

Only issue I am facing is to assign multiple variable values to selected rows. 

This is the overall summary of my project and what i am trying to accomplish here, I really appreciate @XanderBakker  for your time and you agreeing to help me with it.  

Please let me know if you require more explanation from me on any part of this message. 



0 Kudos
Esri Esteemed Contributor

Hi @HussainMalik1 ,


I had a quick look at the Excel and I noticed some #REF errors in column EV with the formula =$EK15*#¡REF!

There are also references to external information like "Current and Future State Model with 2023 BAU (Jan18).xlsx" that will be needed to see how to best solve this.

New Contributor III

Hi @XanderBakker 

Well the idea of sharing this excel model was to give you a high level idea of what i am dealing with and amount of variables and calculated fields gonna be involved in my GIS model, this excel model is kind of a reference for  my ArcGIS model. As far as columns with !REF! goes its not complex calculation and can easily be fixed. 

Also there are more than one workbooks involved in the excel that's where we are taking the variable values from. Also I should have mentioned earlier that in the excel sheet all the COLORED fields are the variables that's why they are being called by VLOOKUP and all the columns with BLACK colored text are calculated field. For your review I have shared the actual excel model in the shared One Drive folder.

And @XanderBakker I really appreciate you agreeing to work with me, so thank you very much.




0 Kudos
Esri Esteemed Contributor

Hi @HussainMalik1 ,


I would probably go for a table that contains all the possible combinations of archetype, year, variable and value. Something like this:

Archetype DescriptionModel YearVariableValue
Large Single Storey Home built < 20072020Elec_Enmax_Archetype_AverageConsumption_[MWh]7,51924737
Large Single Storey Home built < 20072030Elec_Enmax_Archetype_AverageConsumption_[MWh]7,51924737
Large Single Storey Home built < 20072040Elec_Enmax_Archetype_AverageConsumption_[MWh]7,51924737
Large Single Storey Home built < 20072050Elec_Enmax_Archetype_AverageConsumption_[MWh]7,51924737
Large Single Storey Home built < 20072020NatGas_Building_Consumption_Calibrated_[MWh]0
Large Single Storey Home built < 20072030NatGas_Building_Consumption_Calibrated_[MWh]0
Large Single Storey Home built < 20072040NatGas_Building_Consumption_Calibrated_[MWh]35,0111111
Large Single Storey Home built < 20072050NatGas_Building_Consumption_Calibrated_[MWh]35,0111111


This could be read by the script to create a nested dictionary with this structure:

{Archetype: {Year: {Variable: Value}}}


When in the script you need a value you can access it using:

value = dct[Archetype][Year][Variable]
0 Kudos