This will effectively build everything we need in an underlying query to create the CASE STATEMENT we did above, in Chartio’s Data Pipeline.Īdding a CASE STATEMENT pipeline step allows us to set the conditions for the WHEN and the ELSE just like we did before, without having to type in the entire SQL syntax. Then drag ‘Created Date’ and ‘Provider’ to the dimensions box and re-label them ‘Date’ and ‘Email Provider.’ After that, using the ‘Created Date’ column you can set the date span (or build your WHERE clause) to be everything after. First, let’s build the query.ĭrag the ‘Clicks Column’ to the measures box and aggregate it by TOTAL SUM of the Column Clicks, then re-label it ‘CLICKS.’ After building our underlying query to pull in all the columns we’re going to need SUM OF CLICKS, DATE and EMAIL ADDRESS we can use the Data Pipeline to manipulate this data post-SQL. In using Chartio, we can do all of the above without writing any SQL but leveraging the Data Explorer and the Data Pipeline features.
Then after adding a PIVOT DATA step into the Data Pipeline, we’ll get a table properly arranged in the proper format to set up a line chart showing how clicks are compared over time.
When you piece all three of those columns for one SELECT STATEMENT and throw in the rest of the necessary pieces to build a SQL query, it all take shape below. The resulting table of this CASE STATEMENT with corresponding emails alone. "Provider" = 'Gmail' THEN 'Gmail'Īnd, the else statement would be ‘Other’ for every other email address provider.
0 kommentar(er)
