Now let's add the SQL to the script. The code that can be found at https://github.com/PacktPublishing/Hands-On-Business-Intelligence-with-Qlik-Sense/blob/master/Chapter08/02%20-%20Retrieve%20Script.qvs. Please copy and paste it into the Script editor:

//Precedent load to format the field trip_month
NYC_Bike_trips:
load 
   date(trip_month) as trip_month
   ,line_counter
   ,start_station_name
   ,end_station_name
   ,tripduration
   ,trips;
   
SQL // SQL to retrieve summarized data 
SELECT 
    TIMESTAMP_TRUNC(starttime, month) AS trip_month // Converts a date to month
  ,start_station_name
  ,end_station_name
  ,sum(tripduration) AS tripduration // aggregates the total duration
  ,count(starttime) AS trips // aggregates the total count of trips 
  ,sum(1) as line_counter // line counter
FROM `bigquery-public-data.new_york.citibike_trips`
GROUP BY 
     TIMESTAMP_TRUNC(starttime, month) // Converts a date to month
  ,start_station_name
  ,end_station_name;

This script connects using our newly-created connection, then executes a SQL statement with a GROUP BY clause summarizing our data by month and start/end stations, summing up the duration and counting how many trips were done.

Save the script and run the code. It will take no more than a few minutes to retrieve approximately 3,000,000 records:

Started loading data

Connected
NYC_Bike_trips << QueryResult
Lines fetched: 2,999,575 
Creating search index
Search index creation completed successfully

App saved

Finished successfully
0 forced error(s)
0 synthetic key(s)

Create a dashboard with graphs, a filter panel, some KPIs, and a bar chart, similar to the following. We left all expressions, such as sum (tripduration), as headers to make it easier to reproduce them. This application can be downloaded from the GitHub (https://github.com/PacktPublishing/Hands-On-Business-Intelligence-with-Qlik-Sense/blob/master/Chapter08/NYC%20Bikes.zip).