Hi I have two apps
1. Selection Apps
2. Template app
Selection App scritrs
LET FLIGHTS_QVD='[$(FOLDER)/FlightSummaryUnder100k.qvd] (qvd)';
LET AIRLINES_QVD='[$(FOLDER)/AirlinesUnder100k.qvd] (qvd)';
LET ORIGINS_QVD='[$(FOLDER)/OriginAirportsUnder100k.qvd] (qvd)';
LET DESTS_QVD='[$(FOLDER)/DestAirportsUnder100k.qvd] (qvd)';
LET FARES_QVD='[$(FOLDER)/FaresUnder100k.qvd] (qvd)';
// Summary data for measure calculation and charts
FlightSummary:
LOAD *, Year AS YEAR
FROM $(FLIGHTS_QVD);
Airlines:
LOAD *
FROM $(AIRLINES_QVD);
OriginAirports:
LOAD *
FROM $(ORIGINS_QVD);
DestAirports:
LOAD *
FROM $(DESTS_QVD);
Fares:
LOAD *,[Fare Class Name] As FARECLASSNAME
FROM $(FARES_QVD);
Template Scripts
// DO NOT ALTER THIS SUBROUTINE
SUB ExtendQVDWhere(Name, ValVarName)
LET T = Name & '_COLNAME';
LET ColName = $(T);
LET Values = $(ValVarName);
IF (len(Values) > 0) THEN
IF len(WHERE_PART) > 0 THEN
LET WHERE_PART = '$(WHERE_PART) AND mixmatch([$(ColName)],$(Values) )';
ELSE
LET WHERE_PART = ' WHERE mixmatch([$(ColName)],$(Values))';
ENDIF
ENDIF
END SUB;
// DO NOT ALTER THIS SUBROUTINE
SUB ExtendSQLWhere(Name, ValVarName)
LET T = Name & '_COLNAME';
LET ColName = $(T);
LET Values = $(ValVarName);
IF (len(Values) > 0) THEN
IF len(WHERE_PART) > 0 THEN
LET WHERE_PART = '$(WHERE_PART) AND $(ColName) IN ( $(Values) )';
ELSE
LET WHERE_PART = ' WHERE $(ColName) IN ( $(Values) )';
ENDIF
ENDIF
END SUB;
// DO NOT ALTER THIS SUBROUTINE
SUB BuildValueList(VarName, TableName, ColName, QuoteChrNum)
IF ($(QuoteChrNum) = 0) THEN
LET LOADEXPR = 'Concat($(ColName),' & chr(39) & ',' & chr(39) & ') AS CombinedData';
ELSE
LET CHREXPR = ' chr(' & '$(QuoteChrNum)' & ') ';
LET LOADEXPR = 'Concat( $(CHREXPR) & $(ColName) & $(CHREXPR)' & ',' & chr(39) & ',' & chr(39) & ') AS CombinedData';
ENDIF
_TempTable:
LOAD $(LOADEXPR) Resident $(TableName);
Let vNoOfRows = NoOfRows('_TempTable');
IF $(vNoOfRows)> 0 THEN
LET $(VarName) = Peek('CombinedData',0,'_TempTable');
ENDIF
drop table _TempTable;
drop table '$(TableName)';
END SUB;
// CHANGE 1: Update these blocks of INLINE table loads to correspond to the names of the fields from your
// selection app. The contents inside the $() in the record body of the INLINE load statements
// must match the names of the fields from your selection app that the user makes selections on.
// If the database column name (lor QVD field name) for any of the selection fields has a different
// name, you need to alter the right hand side of the SET xxxx_COLNAME statement to reflect that
// field's corresponding database column (or QVD field) name;
//
// All fields for On Demand are prefixed with od and the following to indicate selected or associated
// values
// ods = Selected values
// odo = Associated values
// odso = Selected/associated values
// od = ods = Selected values
//
// With the INLINE table form of ODAG binding, the "quote" and "delimiter" options are used to suppress the OagService's
// default behavior of wrapping each value with single quotation marks and inserting a comma between each value because
// we want the values to be injected into the INLINE tables as a literal list only separated by newlines. This is
// because the local BuildValueList subroutine takes care of comma separating the values and quote wrapping in those
// cases that need it (passing 39 for the 4th parameter of BuildValueList causes it to wrap each value with single
// quotes whereas passing 0 suppresses quote wrapping which is what we do in the case of numeric values since SQL
// doesn't require numbers to be quoted).
SET ORIGIN='';
OdagBinding:
LOAD * INLINE [
VAL
MKK
HNL
OGG
LNY
KOA
LIH
ITO
];
SET ORIGIN_COLNAME='Origin Code';
// SET ORIGIN_COLNAME='ORIGIN'; // SQL version
CALL BuildValueList('ORIGIN', 'OdagBinding', 'VAL', 39); // 39 is for single quote wrapping values
SET DEST='';
OdagBinding:
LOAD * INLINE [
VAL
LIH
OGG
];
SET DEST_COLNAME='Destination Code';
// SET DEST_COLNAME='DEST'; // SQL version
CALL BuildValueList('DEST', 'OdagBinding', 'VAL', 39); // 39 is for single quote wrapping values
SET YEAR='';
OdagBinding:
LOAD * INLINE [
VAL
2013
2015
2014
];
SET YEAR_COLNAME='Year';
// SET YEAR_COLNAME='YEAR'; // SQL version
CALL BuildValueList('YEAR', 'OdagBinding', 'VAL', 0); // 0 is for no wrapping of values since years are numeric
SET QUARTER='';
OdagBinding:
LOAD * INLINE [
VAL
2
1
3
4
];
SET QUARTER_COLNAME='Quarter';
// SET QUARTER_COLNAME='QUARTER'; // SQL version
CALL BuildValueList('QUARTER', 'OdagBinding', 'VAL', 0); // 0 is for no wrapping of values since quarters are numeric
SET TICKET_CARRIER='';
OdagBinding:
LOAD * INLINE [
VAL
YV
WP
];
SET TICKET_CARRIER_COLNAME = 'Ticket Carrier Code';
// SET TICKET_CARRIER_COLNAME = 'TICKET_CARRIER'; // SQL version
CALL BuildValueList('TICKET_CARRIER', 'OdagBinding', 'VAL', 39); // 39 is for single quote wrapping values
SET FARE_CLASS='';
OdagBinding:
LOAD * INLINE [
VAL
X
Y
G
F
D
U
];
SET FARE_CLASS_COLNAME='Fare Class';
// SET FARE_CLASS_COLNAME='FARE_CLASS'; // SQL version
CALL BuildValueList('FARE_CLASS', 'OdagBinding', 'VAL', 39); // 39 is for single quote wrapping values
// CHANGE # 2: Insert your SQL connection's CONNECT statement here if using a SQL database source. Note that if you switch
// to using a SQL source, the call to ExtendQVDWhere in the FOR EACH loop below should be replaced with
// a call to the ExtendSQLWhere subroutine. The LOAD statements must also be changed to use SELECT.
// CHANGE #3: Alter this with a leading 'WHERE <condition>' if you want your main FLIGHT table load statement (below)
// to have a non-changing WHERE clause in addition to the clauses that will be inserted
// by the selection app (it is fine to leave it as is).
SET WHERE_PART = '';
// CHANGE 4: Update the list of field names here in the FOR EACH list below to reflect the names of the fields that are
// bound to your selection app in the INLINE binding above.
// Note that in this case we're using ExtendQVDWhere which uses Qlik's "mixmatch" function to build a
// WHERE clause to test whether the inbound records match the conditions. If modifying this template to
// to filter on a SQL SELECT statement, you will need to replace the call to ExtendQVDWhere with a call to
// ExtendSQLWhere (for more details on converting to using SQL, see "Note" in "Change # 5" below).
FOR EACH fldname IN 'ORIGIN', 'DEST', 'YEAR', 'QUARTER', 'TICKET_CARRIER', 'FARE_CLASS'
LET vallist = $(fldname);
IF (IsNull(vallist)) THEN
LET vallist = '';
ENDIF
IF (len(vallist) > 0) THEN
CALL ExtendQVDWhere('$(fldname)','vallist');
// CALL ExtendSQLWhere('$(fldname)','vallist'); // use this version for SQL
ENDIF
NEXT fldname
TRACE Generated WHERE clause: ;
TRACE $(WHERE_PART);
// CHANGE # 5: Create a new folder connection pointing to the QVD location (by default stored in
// C:\ProgramData\Qlik\Examples\OnDemandApp) and then change this setting to refer to that folder location.
LET FOLDER='lib://ODAG_apps (bontonchat_exponentia1)';
LET FLIGHTS_QVD='[$(FOLDER)/FlightsUnder100k.qvd] (qvd)';
LET AIRLINES_QVD='[$(FOLDER)/AirlinesUnder100k.qvd] (qvd) WHERE Exists("Ticket Carrier Code","Ticket Carrier Code")';
LET ORIGINS_QVD='[$(FOLDER)/OriginAirportsUnder100k.qvd] (qvd) WHERE Exists("Origin Code","Origin Code")';
LET DESTS_QVD='[$(FOLDER)/DestAirportsUnder100k.qvd] (qvd) WHERE Exists("Destination Code","Destination Code")' ;
LET FARES_QVD='[$(FOLDER)/FaresUnder100k.qvd] (qvd) WHERE Exists("Fare Class","Fare Class")';
// Note: If you want to convert this script to reading data from a SQL source instead of QVDs, do the following:
//
// a. Comment out each of the SET xxxx_COLNAME statements above and remove the comment on the 'Sql version' of
// each that follows on the next line
//
// b. Comment out the above LET statements
//
// c. Change the line in the FOR EACH loop above that reads:
// CALL ExtendQVDWhere('$(fldname)','vallist');
// to use the following instead:
// CALL ExtendSQLWhere('$(fldname)','vallist');
//
// d. Comment out all of the LOAD QVD statements below and uncomment the 'SQL version's LOAD SQL statements that are
// below each LOAD QVD statement
//
// e. Replace the following commented-out LIB CONNECT statement with a LIB CONNECT statement using a connection
// to a database that contains the flight tables.
//
// LIB CONNECT TO 'MYSQL_CONNECTION (dom_user)';
// CHANGE # 6: Modify the list of columns (or QVD fields) you wish to load from your database table (or QVD)
// but leave the the $(WHERE_PART) portion of SQL (or LOAD) statement alone at the end.
//
// Note that you can have more than one of these dynamically modified SELECT (or LOAD) statements
// by replicating the sections from CHANGE #2 thru this change #5 and customize which WHERE clauses
// will be inserted by altering the list of fields in the FOR EACH statement in Change #3.
// QVD version:
LOAD *
FROM $(FLIGHTS_QVD)
$(WHERE_PART);
// SQL version:
// Flights:
// LOAD "FARE_CLASS" as "Fare Class",
// "ORIGIN_STATE_ABR" as "Origin State",
// "DEST_STATE_ABR" as "Destination State",
// "QUARTER" as "Quarter",
// "ORIGIN" as "Origin Code",
// "DEST" as "Destination Code",
// "TICKET_CARRIER" as "Ticket Carrier Code",
// "YEAR" as "Year",
// "FLIGHT_COUNT",
// "PASSENGERS",
// "DISTANCE",
// "MKT_ID";
// SQL SELECT
// "MKT_ID",
// "YEAR",
// "QUARTER",
// "ORIGIN",
// "ORIGIN_STATE_ABR",
// "DEST",
// "DEST_STATE_ABR",
// "TICKET_CARRIER",
// "FARE_CLASS",
// "PASSENGERS",
// "DISTANCE",
// 1 AS "FLIGHT_COUNT"
// FROM SAPH7T."/QT/AIRPORT_FACT"
// $(WHERE_PART);
// Replace with dimension extraction script
// QVD version:
Airlines:
LOAD *
FROM $(AIRLINES_QVD);
// SQL version:
// Airlines:
// LOAD TICKET_CARRIER as "Ticket Carrier Code",
// "Description" as Airline
// WHERE Exists("Ticket Carrier Code","TICKET_CARRIER");
// SQL SELECT
// "TICKET_CARRIER",
// "Description"
// FROM "SAPH7T"."/QT/CARRIERS";
// QVD version:
OriginAirports:
LOAD *
FROM $(ORIGINS_QVD);
// SQL version:
// OriginAirports:
// LOAD "Code" as "Origin Code",
// "Description" as "Origin Name"
// WHERE Exists("Origin Code","Code");
// SQL SELECT "Code",
// "Description"
// FROM "SAPH7T"."/QT/AIRPORT_CODE";
// QVD version:
DestAirports:
LOAD *
FROM $(DESTS_QVD);
// SQL version:
// DestAirports:
// LOAD "Code" as "Destination Code",
// "Description" as "Destination Name"
// WHERE Exists("Destination Code","Code");
// SQL SELECT "Code",
// "Description"
// FROM "SAPH7T"."/QT/AIRPORT_CODE";
// QVD version:
Fares:
LOAD *
FROM $(FARES_QVD);
// SQL version:
// Fares:
// LOAD FARE_CLASS as "Fare Class",
// "Fare_Class_Description" as "Fare Class Name"
// WHERE Exists("Fare Class","FARE_CLASS");
// SQL SELECT "FARE_CLASS",
// "Fare_Class_Description"
// FROM "SAPH7T"."/QT/FARE_CLASS";
// CHANGE # 7 (optional): This optional change is to add any additional fixed data load or script code
// your application may need.
