CODEWITHSUNDEEP
sqlsql-servergoogle-bigquerybigdatatableau-api
claudiu lujanschi
claudiu lujanschi

Reputation: 61

Insert the timestamp column in the scandate column?

I want to insert the timestamp column in the values of ScanDate column with Null values.

I need to use this in tableau and if I keep the values separetade, I got wrong results

select distinct A.RootDocId, A.LastEventAppId, A.LastEventStatus, 
        case when B.Value = '' then A.Timestamp else 
        PARSE_TIMESTAMP('%d/%m/%Y %H:%M:%S', 
replace(regexp_replace(B.Value, '[/-]1([7-9]) ', '/201\\1 '), '-', '/'))
        end ScanDate, C.Value Federation, A.timestamp 
from `serv.dam.documentroot` A 
left join unnest(Metadata) B ON B.Key like 'ScanDate' 
left join unnest(Metadata) C ON C.Key like '%ederation'
        limit 100

I expected this :

enter image description here

Where Scandate is null, it should be replaced with the timestamp.

Upvotes: 0

Views: 78

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Where Scandate is null, to be replaced with the timestamp

You should use COALESCE function

as COALESCE(Scandate, timestamp)

Or you can just "fix" your query (B.Value = '' --> IFNULL(B.Value, '') = '') as below 

select distinct A.RootDocId, A.LastEventAppId, A.LastEventStatus, 
        case when IFNULL(B.Value, '') = '' then A.Timestamp else 
        PARSE_TIMESTAMP('%d/%m/%Y %H:%M:%S', 
replace(regexp_replace(B.Value, '[/-]1([7-9]) ', '/201\\1 '), '-', '/'))
        end ScanDate, C.Value Federation, A.timestamp 
from `serv.dam.documentroot` A 
left join unnest(Metadata) B ON B.Key like 'ScanDate' 
left join unnest(Metadata) C ON C.Key like '%ederation'
        limit 100

Upvotes: 2

Alex Blakemore
Alex Blakemore

Reputation: 11896

Its usually better when using Tableau to model your relationships (joins) in your Tableau data source, and let the tool generate optimized SQL for your specific visualization. If you hand write your own SQL, Tableau will respect it of course, but not try to optimize it.

Almost anything you would do in mainstream SQL is available in Tableau without resorting to hand written SQL if you decide to explore that route. The Tableau calculation equivalent for SQL's Coalesce() is called IF_NULL().

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37347

Try this:

select RoodDocId,
       LastEventAppId,
       LastEventStatus,
       coalesce(ScanDate, timestamp),
       Federation,
       timestamp
from (
    -- here your whole query that you have
) a

Upvotes: 3

Related Questions