Substituting null value OR no value OR blank value in Insights table, possible?

[AndrewKinlock]

I created a table from the "Create Relationships" functionality using the "Full" relationship type.  I am trying to generate a New Field that replaces any Null/No Value/Blank rows with a zero, while preserving the other non-null or non-no-values from a particular column.

I know we can test for these values using the fxISNULL or fxISNOTNULL, but I am having difficulty leveraging this to populate/replace no values/nulls/blank.

Any advice or solutions for this?

[Ian_Muehlenhaus]

Hi @AndrewKinlock ,

Great question. I’m sorry for your issue. Quick question: have you tried using Data Engineering tool in Insights Desktop? This may be the easiest way to try and fix the problem. You may be able to replace the values right in the existing column, actually. You can simply use the drop-down “Find and Replace” tool in Data Engineering to find “” or Null and replace it with a “0.” You may have to convert the field to string first, which you can do with the drop down in Data Engineering, and then convert it back to number when you’re done. But it should work.

But off the cuff, and sorry if this is leading you astray, I think may try something like this in Online or Enterprise as well:

Glossary of terms you should change to be your field names and values below:

yourField = the field in your dataset with Nulls/No Values/Blank rows that you want to change.

[AndrewKinlock]

Thank you @Ian_Muehlenhaus for the quick response.  I waited for our IT to install Insights Desktop and from the documentation it seems it is used to prepare the data prior to running analysis, but this particular issue occurs from previous "Create Relationship" analyses in the workflow.

I should add that I have a scheduled report update for these results, but I do not see an option in the Data Engineering tab of the application to Publish, which I could then schedule an update.  The shared AGOL Insights report is updating weekly to get new data from two feature services (in AGOL) that are changed almost daily.  How would Data Engineering work in a situation like this?

I was though able to switch myField from a Number to a String and used this function in Insights Online: SUBSTITUTE(myField,"","0")

The output from that function substitutes the blank row as: <Empty-">

Was the correct Insights function used properly?

[Ian_Muehlenhaus]

Awesome! And apologies you waited for IT only to figure it out on your own anyway. My bad! 🙂

I believe it is the correct function! 🙂

This should work, in my opinion, if <Empty> is an okay result for your situation. However, I must admit, there will be people far more expert at this on here than myself, so they may have additional solutions. (I'm a bona fide hack when it comes to writing efficient functions.)

Thanks so much for asking your question in the Esri Community and for using ArcGIS Insights! 

[AkshayHarshe · Accepted solution]

@AndrewKinlock If you would like to use ISNULL or ISNOTNULL you can choose to do something like IF(ISNULL(<Field>), "Text",<Field>). ISNULL(FIELD) will return "True" Or "False" for the sake of the IF function or 0/1 without it.

It makes sense to create a new Calc field in the analysis if you would like to support scheduled refresh. The Data Engineering is not yet at a stage where it can be scheduled to refresh. Though will be under consideration if/when DE makes it to ArcGIS Enterprise.

Hope that Helps