Let's put all these concepts (binding variables with a possible long list of selections) together to create a script that will recover the detailed records from BigQuery according to the selected values of some fields.
Let's assume that the user can make selections into trip_month, start_station_name, and end_station_name, and that we're applying some restrictions:
// Numbers in [] will force the user to make selections
// [1-10] at least one value selected and no more than 10
// Restriction Setup
//$(odso_trip_month)[1-4]
//$(odso_start_station_name)[1-10]
//$(odso_end_station_name)[1-10]
Trace Collecting values for selected values;
// Concatenating all selections from field "StartStations" and delimiting with a quote
StartStations:
LOAD concat(Station,chr(39)&','&chr(39)) as concatenated_stations Inline [
Station
$(odso_start_station_name){"quote": "", "delimiter": ""}
];
Let vStartStations='';
if NoOfRows('StartStations') > 0 then
LET vStartStations = chr(39)&Peek('concatenated_stations')&chr(39);
Endif
Drop Table StartStations;
// Concatenating all selections from field "EndStations" and delimiting with a quote
EndStations:
LOAD concat(Station,chr(39)&','&chr(39)) as concatenated_stations Inline [
Station
$(odso_end_station_name){"quote": "", "delimiter": ""}
];
Let vEndStations='';
if NoOfRows('EndStations') > 0 then
LET vEndStations = chr(39)&Peek('concatenated_stations')&chr(39);
Endif
Drop Table EndStations;
// Concatenating all selections from field "TripMonths" and delimiting with a quote
// We have to adjust date format so we're using subfield to convert to YYYY-MM-DD
TripMonths:
LOAD concat(
SubField(TripMonths,'/',3) & '-'
& SubField(TripMonths,'/',1) & '-'
& SubField(TripMonths,'/',2)
,chr(39)&','&chr(39)) as concatenated_months
Inline [
TripMonths
$(odso_trip_month){"quote": "", "delimiter": ""}
];
Let vMonths='';
if NoOfRows('TripMonths') > 0 then
LET vMonths = chr(39)&Peek('concatenated_months')&chr(39);
Endif
Drop Table TripMonths;
Now we are creating the final SQL query that will be submitted to Google BigQuery:
Trace Creating dynamic SQL;
// Basic SQL for retrieve all fields
SET SQL = 'SELECT * FROM 'bigquery-public-data.new_york.citibike_trips' WHERE 1=1 ';
// If the user has selected StartStations we add a condition
IF Len(vStartStations) > 0 Then
LET SQL = SQL & chr(10) & ' AND start_station_name in ($(vStartStations))';
Endif
// If the user has selected vEndStations we add a condition
IF Len(vEndStations) > 0 Then
LET SQL = SQL & chr(10) & ' AND end_station_name in ($(vEndStations))';
Endif
// If the user has selected TripMonths we add a condition
IF Len(TripMonths) > 0 Then
LET SQL = SQL & chr(10) & ' AND TIMESTAMP_TRUNC(starttime, month) in ($(vMonths))';
Endif
And now we are executing the script:
Trace Executing SQL $(SQL);
LIB CONNECT TO 'Google_BigQuery (qmi-qs-sn_qlik)';
SQL $(SQL);
After pasting all this code, please save your application using Ctrl + S.