CODEWITHSUNDEEP
google-bigquery
NikM
NikM

Reputation: 173

BigQuery - Replace null value depening on condition

I've 2 tables 1&2. Inside table1, I've created a derived field "actualoutput" against which I'm using to join with table-2 with "newgroup" as a connector, to get field "finalgroup".

Now in the output table, the field "finalgroup" has NULL values, as highlighted in blue.

What I want is - ONLY when the value is NULL in "finalgroup", I want to replace with condition based on the field "website" present in table1 and create new column "finalgroup2". So if "website" is present then finalgroup2 will have value "web" else "offline" (highlighted in red) for all others retain value of "finalgroup".

But I'm not getting logic (tried multiple attempts) to get output as shown in the image - FinalTable. If it is possible to get this logic in "finalgroup" field directly, then I'm fine with it i.e. no need of "finalgroup2"

Can someone please help.

Refer image:

enter image description here

Query I used:

SELECT
  source,
  detail,
  CONCAT(IFNULL(Source,
      ''), "-", IFNULL(Detail,
      '')) AS actualoutput,
  website,
  newgroup,
  finalgroup,
  CASE
    WHEN finalgroup IS NULL AND website IS NOT NULL THEN "web"
    WHEN finalgroup IS NULL
  AND website IS NULL THEN "offline"
  ELSE
  "finalgroup"
END
  AS finalgroup2
FROM (
  SELECT
    source,
    detail,
    CONCAT(IFNULL(Source,
        ''), "-", IFNULL(Detail,
        '')) AS actualoutput,
    website,
    newgroup,
    finalgroup
  FROM (
    SELECT
      source,
      detail,
      CONCAT(IFNULL(Source,
          ''), "-", IFNULL(Detail,
          '')) AS actualoutput,
      website
    FROM (
      SELECT
        source,
        detail,
        website
      FROM
        `dataset.concat`))
  LEFT JOIN (
    SELECT
      newgroup,
      finalgroup
    FROM
      `dataset.finalgroup`)
  ON
    actualoutput=newgroup)

Upvotes: 1

Views: 5999

Answers (1)

rmesteves
rmesteves

Reputation: 4075

Try doing that:

WITH
  tnew AS(
  SELECT
    source,
    detail,
    CONCAT(IFNULL(Source,
        ''), "-", IFNULL(Detail,
        '')) AS actualoutput,
    website
  FROM
    table1 )
SELECT
  source,
  detail,
  website,
  actualoutput,
  finalgroup,
  CASE
    WHEN finalgroup IS NOT NULL THEN finalgroup
    WHEN finalgroup IS NULL
  AND website IS NOT NULL THEN "web"
    WHEN finalgroup IS NULL AND website IS NULL THEN "offline"
END
  finalgroup2
FROM
  tnew
LEFT JOIN
  table2
ON
  actualoutput = newgroup

Please let me know if it helps you

Upvotes: 1

Related Questions