topic Re: Dynamic Views from Excel in App Development

Dynamic Views from Excel

Christos500 — Thu, 16 May 2024 06:25:30 GMT

greetings,

does anyone know if it is possible to create a template app for a dynamic view solution that refreshes the data from an excel file instead of a database or a qvd ?

thank you

Re: Dynamic Views from Excel

JHuis — Thu, 16 May 2024 12:50:10 GMT

Shure, thats no problem. It is just al reload and you can use every source.

Re: Dynamic Views from Excel

Christos500 — Fri, 17 May 2024 07:36:43 GMT

so what's the process? First load from an excel file, then convert it to qvd and then read the qvd ?

because in the template app script we have only commands like ' CALL ExtendQVDWhere('$(fldname)','vallist') ' for qvd case or ' CALL ExtendSQLWhere('$(fldname)','vallist') ' for sql database.

Re: Dynamic Views from Excel

Christos500 — Mon, 27 May 2024 09:26:31 GMT

i tried to first have a script section in my tempalte app that i convert my excel files into qvds and then in another section the dynamic views code below . Then i create a dynamic view in a selection app i click the refresh button of the dynamic view and its working. But the problem arises when i try to add a new value in my excel file that the dynamic view can't show. From what i guess is that the selection app will get the values from the template's app qvds and wont first run the conversion from the excel file .

// 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 OfficeID ='';
OdagBinding:
LOAD * INLINE [
VAL
$(odo_OfficeID){"quote": "", "delimiter": ""}
];
SET OfficeID_COLNAME='OfficeID';
CALL BuildValueList('OfficeID', '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 'OfficeID'
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)
$(WHERE_PART);

Office:
load
OfficeID,
OfficeAddress,
OfficeCity,
OfficeCountry,
OfficeFax,
OfficePhone,
OfficePostalCode,
OfficeStateProvince
FROM [lib://DataFiles/Office.qvd](qvd)
where exists(OfficeID)
;

Re: Dynamic Views from Excel

Christos500 — Tue, 28 May 2024 07:31:20 GMT

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:

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

SET WHERE_PART = '';

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);

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)

SET WHERE_PART = '';

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);

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)
;