CODEWITHSUNDEEP
sqlregexgoogle-analyticschartio
Jason
Jason

Reputation: 1

Using SQL & REGEX to Clean String and Cast to Date in Chartio

I don't know too much about SQL and REGEX, especially how they work together. But I've become responsible for using Chartio to visualise data at work and need some help.

In Google Analytics, under Search Terms we capture a date range. When I pull that into Chartio it's a string and unclean, which is almost unusable.

A few examples of how it appears in Google Analytics.

2018-01-08T12:00:00.000Z
2018-01-28T00:00:00.000Z
12-31-2018
Auckland
Christchurch

In Chartio I can create a Data Store where I take the data from Google Analytics and can manipulate it.

I can create a custom column in the schema to convert the string into a Date using this command, as suggested by Chartio here

CAST("Dates"."ga:searchKeyword" as date)

But I need to clean the data first so that I only valid dates. My poor attempt at creating a command looks like this

CASE WHEN REGEXP_SUBSTR("(19|20)\d\d[-/.](0[1-9]|1[012])[-/.](0[1-9]|[12][0-9]|3[01])") THEN CAST("Dates"."ga:searchKeyword") AS DATE

I know my attempt is wrong, because it doesn't work and also I don't know what I am doing.

Please help!

Upvotes: 0

Views: 380

Answers (0)

Related Questions