CODEWITHSUNDEEP
nullgoogle-bigquery
user3577904
user3577904

Reputation: 491

BigQuery - NULL values

I want to make calculation among columns, which contains null values

x1     x2
9      0.0
5      1.2
12     null
10     null

If calculation 

x1 + (x1*x2)

is made, it results in

9, 6, null, null

Can you pls suggest, how null values can be handled, so the result will be

9, 6, 12, 10

I was trying ifelse, if value is null, then use 1

IF(x1 = null, 0, x1)

but the results is still with null values.

Thank you!

Upvotes: 25

Views: 76208

Answers (3)

geekidharsh
geekidharsh

Reputation: 3589

Use ifnull function in Google Big Query. (Link provided)

IFNULL(x1, 0)*IFNULL(x2,0) as new_col
  • This will plug in 0 in place of any null values in x1 and x2.

FYI: There is a ISNULL(column_name) in Google cloud Data Prep just like in MySQL that will return a boolean i.e either True or False depending upon the column is null or not.

Upvotes: 6

norman123123
norman123123

Reputation: 987

Another possible solution is using COALESCE(arg1, arg2, ...,argN) Which returns the first not null argument.

For example:

COALESCE(x1 , 0) * COALESCE(x2, 0) returns 0 if x1 (or x2) is null.

Upvotes: 2

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

Use IFNULL(expr, 0) - this will come back as 0 if expr is null.

In general, instead of doing something=null do something IS null.

Upvotes: 45

Related Questions