Has anyone else experienced issues when creating calculated fields using division in the data preparation environment?
Yesterday at 11AM UK time a dataset which had previously been working fine all of a sudden got stuck in an ingestion phase and as such would not start a new ingestion or fail. This is probably a case where cancelling a data refresh that’s in progress would be helpful, but I digress.
I attempted to alter the query, but no luck. I then recreated the dataset, no dice. I was getting an error telling me that one of the date columns was getting values that were not in proper datetime format as specified by the schema, which was weird. I checked the data in the database, everything looked normal. I specifically checked the rows provided in the error file, but the columns returned by the query didn’t match what I saw in the file. I removed the aggregation I had in my query on the date, still had an issue. I then removed the date column entirely. Now I had a new error, telling me that the number of exceptions exceeded the limit.
I then copy/pasted the SQL query itself into a new dataset and it worked. I added in all of my calculated columns, thinking that something must have just fallen over and the dataset was bricked. But the error came back.
Realizing that I probably got a bit ahead of myself adding all of the calculated fields at once. I removed them all and added then published the dataset one field at a time. One by one, they all went back in, until I added a calculated field where I was doing division. There were 3 fields where I was doing division, and none of them employed dates in any capacity. These fields used to work, but for some reason adding any one of them would make it so the dataset coulf no longer update itself. I even tried creating a field that was just 1/2, and again the dataset failed to load.
After finding the problem, removing the offending fields so that the dataset would continue to update, and then getting some sleep, after some further sanity checking to make sure this was actually a problem, I found a solution, which was to calculate the fields within the analysis, rather than at the data preparation stage. This worked, for reasons unknown to me.
Multiplication works. Addition and subtraction works. Just don’t divide. Has anyone else experienced this issue??
TLDR: If you want to do division on any fields in your dataset, it’s probably best to do those calculations within the individual analysis instead.
