Merging 3 columns into 1

Hi,

I am trying to merge together 3 columns into one, using the following syntax, however, I get an error. Does anyone know why? thanks!

dimension: total_IDs {
type: string
sql: SELECT total_id FROM
(
SELECT b_cust_id AS total_id FROM ${TABLE}.b_cust_id
UNION ALL
SELECT l_cust_id AS total_id FROM ${TABLE}.l_cust_id
UNION ALL
SELECT u_cust_id AS total_id FROM ${TABLE}.u_cust_id);;
}

Hi Miruna,

I guess this is going back to yesterday’s query! I’ve just had a read up on this and it seems I was mistaken - rather than a custom dimension, you would need to set up a view. So for this the structure would be something like:

view: total_ids
derived_table: {
SELECT idlist1 FROM table
UNION
SELECT idlist2 FROM table
UNION
SELECT idlist3 FROM table
}

This should give you a table with one row (named by default idlist1 unless you rename this by using AS …). You could then set up a dimension referring to ${TABLE}.idlist1 as with any other standard dimension.

Joining this onto an existing explore is a different matter, although if it’s just a list you want then that’s a way!

n.b. I haven’t tried this in Looker although Google BigQuery prompted me to use UNION DISTINCT as opposed to just “UNION”, so if that doesn’t work try that!

That’ great, this is what I need.

Thanks for your help!