I just found the solution by myself. First you need to have a script section in the template app that loads the data from excel file/s and then convert it/them into qvd/s . Then you have to put the dynamic views code below but we have two cases :
1) Case with a star schema (dimension and fact tables ) and
2) Case with one table ( for many reasons we ve decided to join all of our tables with qvds, excel etc into one single table ).
For the 1) case we need to put the $(WHERE_PART) clause into the fact table but for the 2) case we must not !!!
Below the code for the dynamic views :
1) case:
// 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
// Shopping Cart app. The contents inside the $() in the record body of the INLINE load statements
// must match the names of the fields from your shopping cart 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 corrresponding 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/assocaited values
//
//
SET EmpID ='';
OdagBinding:
LOAD * INLINE [
VAL
$(odo_EmpID){"quote": "", "delimiter": ""}
];
SET EmpID_COLNAME='EmpID';
CALL BuildValueList('EmpID', 'OdagBinding', 'VAL', 39); // 39 is for single quote wrapping values
// CHANGE #2: Alter this with a leading 'WHERE <condition>' if you want your SQL statement (below)
// to have a non-changing WHERE clause in addition to the clauses that will be inserted
// by the shopping cart app (it is fine to leave it as is).
SET WHERE_PART = '';
// CHANGE 3: Update the list of field names here to reflect each of the field names variables you have on the
// left hand side (assignment target) of the first SET statement of the SET statement pairs in change
// 1 above. Note that in this case we're using ExtendQVDWhere which uses Qlik's mixmatch to build a
// where clause to test whether the inbound records match the conditions. If your the LOAD statement
// in which WHERE_PART is applied is querying a SQL database, use the 'ExtendSQLWhere' subroutine
// instead (and, of course, don't forget to include your database CONNECT statement).
FOR EACH fldname IN 'EmpID'
LET vallist = $(fldname);
WHEN (IsNull(vallist)) LET vallist = '';
IF len(vallist) > 0 THEN
CALL ExtendQVDWhere('$(fldname)','vallist');
ENDIF
NEXT fldname
TRACE Generated WHERE clause: ;
TRACE $(WHERE_PART);
// CHANGE # 4: 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 dynamiclly alterered 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.
LET FOLDER='lib://DataFiles';
LET ORDERS_QVD='[$(FOLDER)/Orders.qvd](qvd)';
LET Employees_QVD='[$(FOLDER)/Employees.qvd](qvd)';
Orders:
LOAD
OrderID,
CustomerID,
EmpID,
ShipperID,
FreightWeight,
OrderDate
FROM $(ORDERS_QVD)
$(WHERE_PART);
Employees:
LOAD
EmpID,
"First Name",
"Last Name",
DateOfBirth,
StartDate,
EndDate,
OfficeID,
JobTitle
FROM $(Employees_QVD)
;
for the 2 case)
// 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
// Shopping Cart app. The contents inside the $() in the record body of the INLINE load statements
// must match the names of the fields from your shopping cart 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 corrresponding 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/assocaited values
//
//
SET EmpID ='';
OdagBinding:
LOAD * INLINE [
VAL
$(odo_EmpID){"quote": "", "delimiter": ""}
];
SET EmpID_COLNAME='EmpID';
CALL BuildValueList('EmpID', 'OdagBinding', 'VAL', 39); // 39 is for single quote wrapping values
// CHANGE #2: Alter this with a leading 'WHERE <condition>' if you want your SQL statement (below)
// to have a non-changing WHERE clause in addition to the clauses that will be inserted
// by the shopping cart app (it is fine to leave it as is).
SET WHERE_PART = '';
// CHANGE 3: Update the list of field names here to reflect each of the field names variables you have on the
// left hand side (assignment target) of the first SET statement of the SET statement pairs in change
// 1 above. Note that in this case we're using ExtendQVDWhere which uses Qlik's mixmatch to build a
// where clause to test whether the inbound records match the conditions. If your the LOAD statement
// in which WHERE_PART is applied is querying a SQL database, use the 'ExtendSQLWhere' subroutine
// instead (and, of course, don't forget to include your database CONNECT statement).
FOR EACH fldname IN 'EmpID'
LET vallist = $(fldname);
WHEN (IsNull(vallist)) LET vallist = '';
IF len(vallist) > 0 THEN
CALL ExtendQVDWhere('$(fldname)','vallist');
ENDIF
NEXT fldname
TRACE Generated WHERE clause: ;
TRACE $(WHERE_PART);
// CHANGE # 4: 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 dynamiclly alterered 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.
LET FOLDER='lib://DataFiles';
LET Employees_QVD='[$(FOLDER)/Employees.qvd](qvd)';
Employees:
LOAD
EmpID,
"First Name",
"Last Name",
DateOfBirth,
StartDate,
EndDate,
OfficeID,
JobTitle
FROM $(Employees_QVD)
;
