Dashboard Data Expression changing Null values to 0

[LJackson29]

Hello -

I have a data expression (see below) in ArcGIS Dashboards, and it is changing Null values to 0 for Number fields (for example PR_REDTOTPAR in line 99). Is there a way I can prevent that from happening? I need to be able to filter out the null values in some indicators to obtain the number of records without missing values.

Thanks,

Leila

var port = Portal('https://www.arcgis.com');
var fs = filter(FeatureSetByPortalItem(
  port,'01610f3a8912467c8b06f4b223a6b5e1', 0,
  ['PROJECTID', 'PR_PROJNAME', 'PR_STATUS', 'PR_WAC', 'PR_SUBWATERSHED', 'PR_COMMUNITY1', 'PR_COMMUNITY2', 'PR_SERVAREA', 'PR_ORIGTOTCOST', 'PR_ASPTOTCOST', 'PR_P6TOTCOST', 'PR_EBSTOTCOST', 'PR_PROJYEAR', 'PR_SWIM_ONLY', 'PR_PART_ONLY', 'PR_NONDISTRICT', 'PR_BASIN', 'PR_CROSSING', 'PR_CULVERT', 'PR_MJSTRUCT', 'PR_STREAM', 'PR_RANK', 'PR_EXHYDPAR', 'PR_EXSTRPAR', 'PR_EXTOTPAR', 'PR_ALTHYDPAR', 'PR_ALTSTRPAR', 'PR_ALTTOTPAR', 'PR_REDHYDPAR', 'PR_REDSTRPAR', 'PR_REDTOTPAR', 'PR_TOTSCORE', 'PR_COSTREDPAR','PR_COSTREDPAR_PCTR', 'PR_EXHYDPAR_PCTR', 'PR_EXSTRPAR_PCTR', 'PR_EXTOTPAR_PCTR', 'PR_REDHYDPAR_PCTR', 'PR_REDSTRPAR_PCTR', 'PR_REDTOTPAR_PCTR', 'PR_BESTCOST'],
  true),
  "(PR_STATUS='01. Submitted – Pending Review/Scoring' OR PR_STATUS= '02. Scored' OR  PR_STATUS='03. Advanced SW Planning' OR PR_STATUS='04. Design' OR PR_STATUS='05. Construction' OR PR_STATUS='07. Substantial Completion' OR PR_STATUS='08. Project Closed Out') AND PR_NONDISTRICT='No' AND PR_SWIM_ONLY='No' AND PR_PART_ONLY='No'"
);

var out_dict = {
    fields: [
        {name: 'projectid', type: 'esriFieldTypeString'},
        {name: 'projectname', type: 'esriFieldTypeString'},        
        {name: 'status', type: 'esriFieldTypeString'},
        {name: 'wac', type: 'esriFieldTypeString'},
        {name: 'subwatershed', type: 'esriFieldTypeString'},
        {name: 'community1', type: 'esriFieldTypeString'},
        {name: 'community2', type: 'esriFieldTypeString'}, 
        {name: 'serviceareawide', type: 'esriFieldTypeString'},        
        {name: 'origtotcost', type: 'esriFieldTypeDouble'},
        {name: 'asptotcost', type: 'esriFieldTypeDouble'},
        {name: 'p6totcost', type: 'esriFieldTypeDouble'},
        {name: 'ebstotcost', type: 'esriFieldTypeDouble'},
        {name: 'orig_asptotcost', type: 'esriFieldTypeDouble'},
        {name: 'projyear', type: 'esriFieldTypeString'},
        {name: 'swim', type: 'esriFieldTypeString'},
        {name: 'partner', type: 'esriFieldTypeString'},
        {name: 'nondistrict', type: 'esriFieldTypeString'},
        {name: 'descon', type: 'esriFieldTypeString'},
        {name: 'stream', type: 'esriFieldTypeString'},
        {name: 'culvert', type: 'esriFieldTypeString'},
        {name: 'crossing', type: 'esriFieldTypeString'},
        {name: 'basin', type: 'esriFieldTypeString'},
        {name: 'mjstructure', type: 'esriFieldTypeString'},
        {name: 'rank', type: 'esriFieldTypeInteger'},
        {name: 'exhydpar', type: 'esriFieldTypeDouble'},
        {name: 'exstrpar', type: 'esriFieldTypeDouble'},
        {name: 'extotpar', type: 'esriFieldTypeDouble'},
        {name: 'althydpar', type: 'esriFieldTypeDouble'},
        {name: 'altstrpar', type: 'esriFieldTypeDouble'},
        {name: 'alttotpar', type: 'esriFieldTypeDouble'},
        {name: 'redhydpar', type: 'esriFieldTypeDouble'},
        {name: 'redstrpar', type: 'esriFieldTypeDouble'},
        {name: 'redtotpar', type: 'esriFieldTypeDouble'},
        {name: 'exhydpar_pctr', type: 'esriFieldTypeDouble'},
        {name: 'exstrpar_pctr', type: 'esriFieldTypeDouble'},
        {name: 'extotpar_pctr', type: 'esriFieldTypeDouble'},
        {name: 'redhydpar_pctr', type: 'esriFieldTypeDouble'},
        {name: 'redstrpar_pctr', type: 'esriFieldTypeDouble'},
        {name: 'redtotpar_pctr', type: 'esriFieldTypeDouble'},
        {name: 'costredpar', type: 'esriFieldTypeDouble'},
        {name: 'costredpar_pctr', type: 'esriFieldTypeDouble'},       
        {name: 'totscore', type: 'esriFieldTypeDouble'},  
        {name: 'bestcost', type: 'esriFieldTypeDouble'},        

    ],
    geometryType: '',
    features: []
}

// Iterate over each feature in the layer
for (var f in fs){
    
    Push(
        out_dict['features'],
        {attributes:
            {
                projectid: Text(f['PROJECTID']),
                projectname: Text(f['PR_PROJNAME']),
                status: Text(f['PR_STATUS']),
                wac: Text(f['PR_WAC']),
                subwatershed: Text(f['PR_SUBWATERSHED']),
                community1: Text(f['PR_COMMUNITY1']),
                community2: Text(f['PR_COMMUNITY2']),
                serviceareawide: Text(f['PR_SERVAREA']),
                origtotcost: Number(f['PR_ORIGTOTCOST']),
                asptotcost: Number(f['PR_ASPTOTCOST']),
                p6totcost: Number(f['PR_P6TOTCOST']),
                ebstotcost: Number(f['PR_EBSTOTCOST']),
                orig_asptotcost: IIF(f['PR_ASPTOTCOST']>0, Number(f['PR_ASPTOTCOST']), f['PR_ORIGTOTCOST']),
                projyear: Text(f['PR_PROJYEAR']),
                swim: Text(f['PR_SWIM_ONLY']),
                partner: Text(f['PR_PART_ONLY']),
                nondistrict: Text(f['PR_NONDISTRICT']),
                descon: IIf(f['PR_SWIM_ONLY']=='No' && f['PR_PART_ONLY']=='No' && f['PR_NONDISTRICT']=='No', 'Yes', 'No'), 
                stream: Text(f['PR_STREAM']),
                culvert: Text(f['PR_CULVERT']),
                crossing: Text(f['PR_CROSSING']),
                basin: Text(f['PR_BASIN']),
                mjstructure: Text(f['PR_MJSTRUCT']),
                rank: IIf(f['PR_RANK']>=1, f['PR_RANK'], ''),
                exhydpar: Number(f['PR_EXHYDPAR']),
                exstrpar: Number(f['PR_EXSTRPAR']),
                extotpar: Number(f['PR_EXTOTPAR']),
                althydpar: Number(f['PR_ALTHYDPAR']),
                altstrpar: Number(f['PR_ALTSTRPAR']),
                alttotpar: Number(f['PR_ALTTOTPAR']),
                redhydpar: Number(f['PR_REDHYDPAR']),
                redstrpar: Number(f['PR_REDSTRPAR']),
                redtotpar: Number(f['PR_REDTOTPAR']),
                exhydpar_pctr: Number(f['PR_EXHYDPAR_PCTR']),                
		        exstrpar_pctr: Number(f['PR_EXSTRPAR_PCTR']),
                extotpar_pctr: Number(f['PR_EXTOTPAR_PCTR']),
                redhydpar_pctr: Number(f['PR_REDHYDPAR_PCTR']),
                redstrpar_pctr: Number(f['PR_REDSTRPAR_PCTR']),
                redtotpar_pctr: Number(f['PR_REDTOTPAR_PCTR']),
                costredpar: Number(f['PR_COSTREDPAR']),
                costredpar_pctr: Number(f['PR_COSTREDPAR_PCTR']),
                totscore: Number(f['PR_TOTSCORE']),
                bestcost: Number(f['PR_BESTCOST']),
            }
        }
    )
}

//Return populated out_dict as FeatureSet
var risk = FeatureSet(Text(out_dict))

return risk

[KenBuja]

Is there a reason you're trying to convert those values to a number? The Number function returns a 0 for a null value.

[LJackson29]

@KenBuja The field is a numeric field (Double) in my layer and I want to maintain it as a numeric field so I can do calculations, but some records have missing data and I need to be able to distinguish them from values that are truly 0. Is there a better way to handle a Double field with missing values in a data expression than as a Number?

[DavidSolari]

If the incoming data f isn't already a number, you can triage null values like so: Iif(IsEmpty(f), null, Number(f)).

[LJackson29]

@DaveDavis The incoming data is double.

[KenBuja · Accepted solution]

I ran a test for a double numeric field that has various values, including nulls. I didn't use the Number function when assigning the original attribute to the new feature (line 29)

var jsonDictionary = {
  fields: [
    { alias: "ID", name: "ID", type: "esriFieldTypeString" },
    { alias: "DoubleField", name: "DoubleField", type: "esriFieldTypeDouble" }
  ],
  geometryType: "",
  features: [
    { attributes: { ID: 1, DoubleField: 6168.25 } },
    { attributes: { ID: null, DoubleField: 0 } },
    { attributes: { ID: 1, DoubleField: null } },
    { attributes: { ID: 2 } }
  ]
};

var fs = FeatureSet(jsonDictionary);

var out_dict = {
  fields: [
    { name: "projectid", type: "esriFieldTypeString" },
    { name: "bestcost", type: "esriFieldTypeDouble" }
  ],
  geometryType: "",
  features: []
};

for (var f in fs) {
  Push(
    out_dict["features"],
    { attributes: { projectid: Text(f["ID"]), bestcost: f["DoubleField"] } }
  );
}

return FeatureSet(out_dict);

This is the output table, with nulls remains as null in the double field. 

 

[LJackson29]

@KenBuja Thanks, I thought I needed the Number() in there. I removed that per your example and getting the null values now. Thanks!

[KenBuja]

You shouldn't need the Text function in there either, unless you want use its formatting tools.

[LJackson29]

@KenBuja Thanks, that is helpful to know - I am still learning about data expressions 🙂