Pivot Table creates blanks instead of NULLs

149
3
Jump to solution
01-15-2019 07:46 AM
deleted-user-qpvAI3Fo0MKR
Occasional Contributor III

When running the Pivot Table tool, I end up with blanks (" ") instead of NULLs.

I'm curious if this is the expected behavior of the tool, or if I'm missing some setting somewhere.

This is a problem because the next step in this workflow is to calculate Summary Statistics (Count) of values in each of these fields, and Count treats blanks as valid values.

I realize I can loop through all the fields and change the blanks to NULLs and will do this as a last resort.

Any advice would be greatly appreciated.

0 Kudos
1 Solution

Accepted Solutions
DanPatterson_Retired
MVP Esteemed Contributor

Interesting according to

Pivot Table—Data Management toolbox | ArcGIS Desktop 

You might be running to the text beginning with  a number issue or some variant

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.

but at least you don't have nulls in the source since the tool would fail

The tool fails if the selected Pivot Field contains Null values.

View solution in original post

3 Replies
DanPatterson_Retired
MVP Esteemed Contributor

Interesting according to

Pivot Table—Data Management toolbox | ArcGIS Desktop 

You might be running to the text beginning with  a number issue or some variant

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.

but at least you don't have nulls in the source since the tool would fail

The tool fails if the selected Pivot Field contains Null values.
deleted-user-qpvAI3Fo0MKR
Occasional Contributor III

Thanks Dan - 

these are indeed integers stored as text. Thanks for reading the documentation for me

DanPatterson_Retired
MVP Esteemed Contributor

No problem Josh... someone has to

0 Kudos