Pivot Table turns nulls into 0

[Anonymous User]

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

Thank you,

Randy McGregor

[DanPatterson_Retired]

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

[Anonymous User]

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

[ChrisDonohue__GISP]

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

[Anonymous User]

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

[Anonymous User]

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

[DanPatterson_Retired]

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

[Anonymous User]

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

[DanPatterson_Retired]

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 ""

[Anonymous User]

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