Attribute table is different in ArcPro than in ArcMap or ArcOnline

[CharlieKaufman]

I am trying to use a layer from our State's Department of Health to look at COVID cases. 

https://services2.arcgis.com/XZg2efAbaieYAXmu/arcgis/rest/services/COVID_19_Time_Cases_Eastern_View/...

It is time series of case numbers by county, if you sort the attribute table by date descending you see the example below.

However if you open the data in ArcMap the attribute table looks like this.

Same Case numbers for the FID's but the dates are off.

Now open the same data in ArcPro (preferred option since that is where most of my work is being done).

Same FID's, different dates, all 0's across the board.   

When I contacted the source agency they said it had to do with a CSV error with Pro.   Has anyone else come across this issue?

Charlie

[JoeBorgione]

I don't have ArcMap but I am seeing the exact same thing you are in AGOL and Pro.

All 15,9937 records show <Null> Deaths and 0 Cases. Is the service based on a csv?  Can you get a copy of the data in csv and see what you can display?

[CharlieKaufman]

Unfortunately since the data is coming from another state agency and the nature of the data I can't get a copy of the raw data.  But I appreciate you confirming that you see the same thing that I do.  At least I know I'm not crazy, at least about this anyway.

Retirement sounds good, need to get me some of that someday.

[jcarlson]

Pro and Portal / AGOL handle time zones differently. On the web, it's automatically adjusting the date/time value to match your machine time zone, whereas in Pro, you get to see the actual value being stored. If you look at the service details, you'll see that it's in UTC.

In this case, it looks like the dates are being store without a time, which defaults to 12:00 AM. If you're on the negative side of Universal Coordinated Time / GMT (it looks like you're on EST?), it's going to shift those dates over back before 12:00, showing up as some time the preceding evening.

As for the 0s, that is a bit strange, but I'd guess that has something to do with either the view layer or how the source data was published. The field information on AGOL shows:

• Deaths (type: esriFieldTypeString, alias: Deaths, SQL Type: sqlTypeFloat, length: 256, nullable: true, editable: true)
• Cases (type: esriFieldTypeInteger, alias: Cases, SQL Type: sqlTypeFloat, nullable: true, editable: true)
• Positive_Cases (type: esriFieldTypeInteger, alias: Positive Cases, SQL Type: sqlTypeFloat, nullable: true, editable: true)

Maybe this is normal behavior (I don't work in AGOL that much), but it seems odd that these Esri integers would originate as floats.

[CharlieKaufman]

Well that helps explain the date difference.  Do you think it would help if I asked if they removed the time portion of the date so the day would match up?

Of course that still leaves the 0s to deal with.....

Charlie

[jcarlson]

Unfortunately, the problem is occurring precisely because they are leaving the time out and it's defaulting to midnight. We had this happen with a layer of ours, and we ended up choosing to default the times to noon. If they are not storing a specific time in the source dataset, though, it will assign the midnight default when the data is imported / appended.

[JoeBorgione]

Interesting discussion: I'm not all that familiar with using time enabled data.  Thanks @jcarlson for the insight.

[CharlieKaufman]

I've sent an e-mail to the other agency asking about how they keep their date data.  Will update if I get a response.

Still the big question is why Pro sees the numbers as 0s but Map and Online see the data.   

[jcarlson · Accepted solution]

Well, this certainly gets more curious. I just opened it in Pro again to see if anything came up in the Diagnostic Log, and saw this:

Data! Not only that, but in the service definition, I now see this:

• Cases (type: esriFieldTypeDouble, alias: Cases, SQL Type: sqlTypeFloat, nullable: true, editable: true)
• Positive_Cases (type: esriFieldTypeDouble, alias: Positive Cases, SQL Type: sqlTypeFloat, nullable: true, editable: true)

You may recall (see my earlier post here) that these fields were previously defined as integer types, though the SQL Type seemed a mismatch.

For the field types to change like that, they must be overwriting the data rather than appending. I'd really have to understand their update process to make a good guess here.

Notice, though, that the latest date in the table (3/1/21) has null values. There are certain processes in which a null value is interpreted as a numpy.nan in Python, which is a type of float. The presence of null values may be changing the field type on overwrite.

I'd keep an eye on the data as future updates are pushed. If the numbers disappear again, look at the field details in the service directory; I'd put money on the integer/float mismatch being involved in some way.

Looks like it was last updated at about 8:00 your time. Do we know how often this layer updates? I'd assume daily.

[CharlieKaufman]

Josh

I bet that is it.  The data gets updated each night with a fresh push.  I will suggest to them to keep an eye out for the integer/float mismatch you spotted.

If I hear of anything else I'll let everyone know.

Charlie