CODEWITHSUNDEEP
google-bigquerytalend
GuruGulabKhatri
GuruGulabKhatri

Reputation: 135

Working with null values in Google BigQuery using Talend Big Data

I am working on Google BigQuery and it seems that it doesn't accept null values. So I replaced them with 0s. The problem occurred when calculating average.

For example, consider this file,

A|B|C|D

India|1|4|6

Sri Lanka|8||4

Nepal|3|0|6

Now say, I have to take average of B,C & D: (B+C+D)/3

Now for row with India, this will work fine. (1+4+6)/3

For row with Nepal, (3+0+6)/3

But for row with Sri Lanka, it should be (8+4)/2

But I am replacing null with 0, thus calculation becoming (8+0+4)/3. Which is wrong.

If I replace null with 0s, it gives wrong output.

How do I make things to work such that nulls are ignored while 0s are counted?

Upvotes: 0

Views: 775

Answers (1)

Pentium10
Pentium10

Reputation: 207912

A mix of integers and NULL values will work fine for a field with "integer" type and "nullable" node, but not if the mode is "required" (since this disallows NULL values).

Note that when importing data as CSV, NULL values are represented by an empty field, not by the string "NULL" nor like "\N".

Upvotes: 3

Related Questions