topic Re: Pivot Table turns nulls into 0 in ArcGIS Pro Questions

Pivot Table turns nulls into 0

Anonymous User — Tue, 27 Mar 2018 19:22:55 GMT

Is it possible to "tell" the Pivot Table to preserver null values. It is turning them into 0's.

Thank you,

Randy McGregor

Re: Pivot Table turns nulls into 0

DanPatterson_Retired — Tue, 27 Mar 2018 19:36:48 GMT

In any event, 0, should be the expected outcome, since it represents 0 observations in that juncture.

But to check... Is your pivot table going to a *.dbf or a feature table in a *.gdb?

dbf's don't support null (like shapefiles) if I remember, hence, nulls would be converted to 0

http://pro.arcgis.com/en/pro-app/tool-reference/data-management/pivot-table.htm

I do think that 0 is the correct entry in a pivot table cell

Re: Pivot Table turns nulls into 0

ChrisDonohue__GISP — Tue, 27 Mar 2018 19:41:22 GMT

I don't know if this is the root issue, as it is somewhat what you describe, but there is a warning in the tool usage information that if the field names are not set up a certain way the results will go to zero.

Usage

If the Pivot Field is a text field, its values must begin with a character (for example, a2) and not a number (for example, 2a). If the value of the first record begins with a number, all the output values will be 0.

Source: Pivot Table—Help | ArcGIS for Desktop

Chris Donohue, GISP

Re: Pivot Table turns nulls into 0

Anonymous User — Wed, 28 Mar 2018 13:59:42 GMT

0 is not the correct value if <NULL> is what is in the cell providing the value. <NULL> means no sampling was done for that chemical on that date. 0 means the site was sampled and nothing was found.

The output is an sde feature class, Oracle-based.

Thank you,

Randy McGregor

Re: Pivot Table turns nulls into 0

Anonymous User — Wed, 28 Mar 2018 14:37:55 GMT

One clarification. The "false zeros" are not a faulty calculation of a <NULL> to zero, but rather result in situations where there is no record of a specific chemical (chemicals are the field names) for a particular date. The field named for the chemical that isn't listed at a particular date should have a value of <NULL> by default. The structure of the pivot table results in this cell being there, but there is no value to populate it with, so should be <NULL>.

Tricky devil.

Thanks

Re: Pivot Table turns nulls into 0

Anonymous User — Wed, 28 Mar 2018 14:42:47 GMT

Thanks. Good suggestion, but the field is a numeric field. "Allow Null Values" is "Yes."

Re: Pivot Table turns nulls into 0

DanPatterson_Retired — Wed, 28 Mar 2018 14:46:48 GMT

as long as the results are going to a geodatabase table... other table forms (ie dbf) don't support nulls so a 0 is used

Re: Pivot Table turns nulls into 0

Anonymous User — Wed, 28 Mar 2018 14:56:33 GMT

Facepalm.

The first output is a pivot table in sde and it has appropriate null values!

I ran a "Make XY Events" tool on that table, which put the output in my default geodatabase, which is where the 0's entered the picture.

I've updated my model to put all intermediates in sde to preserve the null values, they will be deleted upon successful completion of the model.

Thank you for your help. I wouldn't have caught my mistake without the coaching.

Randy McGregor

Re: Pivot Table turns nulls into 0

DanPatterson_Retired — Wed, 28 Mar 2018 15:05:39 GMT

nulls are actually bad... unless you can make them obvious.

For numbers, the maximum or minimum possible value of integers or floats are good (largely for programming, but I often use them with gdb tables if I am working with multi format data.

For text... "Hey... wake up!!!" works for me... way better than ""

Re: Pivot Table turns nulls into 0

Anonymous User — Wed, 28 Mar 2018 15:33:20 GMT

I was mistaken. I am getting "false zeros" in the sde output. Shoot. Back to the drawing board...

Re: Pivot Table turns nulls into 0

Anonymous User — Fri, 30 Mar 2018 13:02:09 GMT

This is, apparently, a bug or at least a deficiency in the tool.

NIM101212: The Pivot Table geoprocessing tool displays zeros (0) instead of 'Null' in the output table for no data values even after exporting the table to a geodatabase table or a stand alone database table.

An enhancement has been requested. I've made a python script tool to find cells that should have false zeros and set them to null.

Thanks to all who participated in this discussion.

Randy