I am new to Observable. SQL is the most similar to data tools I’ve used before (mostly SAS but have used some other tools to a lesser degree, and Excel of course).
I have a .csv importing to SQL. One column of numbers imports as string (maybe the bigint problem i saw referenced elsewhere). I convert the SQL to a data table to easily convert the string column to a number or bigint. When the column is formatted as number, the missing values display as NaN, which prevents some subsequent calculations I’d like to be able to do. It also leads to a “outside range” error when aggregating the column.
When the column is formatted as bigint, the missing values display as undefined. Aggregating leads to an error message referencing the undefined values.
My question: how can I either import this column with an appropriate type and/or recode the column to treat the missing values as 0?
I tried with the data table create column function tool but the guides are entirely unhelpful to me for this purpose (sorry). This seems like it should be a super simple if oldvalue>1 then newvalue=oldvalue, else newvalue = 0 but I can not seem to create new columns in SQL cells and the column function tool seems to require a whole javascript function to be written?
Appreciate any help.