In the weekend I tried some new things:
I also decided to completely remake my selection app and template app, so that I could share my code. Unfortunately it still doesn't work, so any help is appreciated.
Here is all my code for the selection app and the template app:
Selection app script:
Tab "Fact":
FactTable:
LOAD
ProductKey,
CustomerKey,
sum(OrderQuantity) as OrderQuantity,
sum(DiscountAmount) as DiscountAmount,
sum(SalesAmount) as SalesAmount,
sum(TaxAmt) as TaxAmt,
sum(Freight) as Freight,
Year(OrderDate) as OrderYear,
Month(OrderDate) as OrderMonth,
Year(ShipDate) as ShipYear,
Month(ShipDate) as ShipMonth
FROM [lib://AttachedFiles/InternetSales_Fact.qvd]
(qvd)
GROUP By
ProductKey,
CustomerKey,
Year(OrderDate),
Month(OrderDate),
Year(ShipDate),
Month(ShipDate);
Tab "Dimensional Data":
LOAD
CustomerKey,
GeographyKey,
Title,
FirstName,
LastName,
BirthDate,
MaritalStatus,
Suffix,
Gender,
EmailAddress,
YearlyIncome,
TotalChildren,
Occupation
FROM [lib://AttachedFiles/Customers.qvd]
(qvd);
LOAD
ProductKey,
ProductSubcategoryKey,
ProductName,
"Color",
ListPrice,
Size,
Weight,
DaysToManufacture
FROM [lib://AttachedFiles/Products.qvd]
(qvd);
LOAD
ProductSubcategoryKey,
ProductSubcategoryName
FROM [lib://AttachedFiles/ProdSubCategory.qvd]
(qvd);
LOAD
GeographyKey,
City,
StateProvinceCode,
StateProvinceName,
CountryRegionCode,
CountryRegionName,
PostalCode
FROM [lib://AttachedFiles/Geography.qvd]
(qvd);
Template app script:
Tab "Dynamic Data":
// DO NOT ALTER THIS SUBROUTINE
SUB ExtendWhere(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 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;
SET WHERE_PART = '';
SET ProductKey ='';
OdagBinding:
LOAD * INLINE [
VAL
$(odso_ProductKey){"quote": "", "delimiter": ""}
];
SET ProductKey_COLNAME='ProductKey';
CALL BuildValueList('ProductKey', 'OdagBinding', 'VAL', 39); // 39 is for single quote wrapping values
SET CustomerKey ='';
OdagBinding:
LOAD * INLINE [
VAL
$(odso_CustomerKey){"quote": "", "delimiter": ""}
];
SET CustomerKey_COLNAME='CustomerKey';
CALL BuildValueList('CustomerKey', 'OdagBinding', 'VAL', 39); // 39 is for single quote wrapping values
FOR EACH fldname IN 'ProductKey', 'CustomerKey'
LET vallist = $(fldname);
WHEN (IsNull(vallist)) LET vallist = '';
IF len(vallist) > 0 THEN
CALL ExtendWhere('$(fldname)','vallist');
ENDIF
NEXT fldname
TRACE Generated WHERE clause: ;
TRACE $(WHERE_PART);
Tab "Fact":
FactTable:
LOAD
ProductKey,
CustomerKey,
SalesOrderNumber,
SalesOrderLineNumber,
OrderQuantity,
DiscountAmount,
SalesAmount,
TaxAmt,
Freight,
OrderDate,
ShipDate
FROM [lib://AttachedFiles/InternetSales_Fact.qvd]
(qvd)
$(WHERE_PART);
Tab "Dimensional Data":
LOAD
CustomerKey,
GeographyKey,
Title,
FirstName,
LastName,
BirthDate,
MaritalStatus,
Suffix,
Gender,
EmailAddress,
YearlyIncome,
TotalChildren,
Occupation
FROM [lib://AttachedFiles/Customers.qvd]
(qvd)
WHERE EXISTS(CustomerKey);
LOAD
ProductKey,
ProductSubcategoryKey,
ProductName,
Color,
ListPrice,
Size,
Weight,
DaysToManufacture
FROM [lib://AttachedFiles/Products.qvd]
(qvd)
WHERE EXISTS(ProductKey);
LOAD
ProductSubcategoryKey,
ProductSubcategoryName
FROM [lib://AttachedFiles/ProdSubCategory.qvd]
(qvd);
LOAD
GeographyKey,
City,
StateProvinceCode,
StateProvinceName,
CountryRegionCode,
CountryRegionName,
PostalCode
FROM [lib://AttachedFiles/Geography.qvd]
(qvd);
When I use the App Navigation Link inside my selection app everything goes great till it reaches status: "loading" and then it goes to status: "failed". When I open the generated app it contains no data. I also tried looking into the logs, but I haven't found anything interesting.
All files mentioned in script can be found here:
https://support.qlik.com/articles/000045642?_ga=2.206967578.849473933.1574004650-1142306390.15317691...
Unrelated question: Does anyone have the example mentioned here:
There are sample on-demand selection and template apps included in the Qlik Sense Enterprise installation at ProgramData\Qlik\Examples\OnDemandApp\sample.
Because I can't seem to find this.
@Ian_Crosland You were mentioned on slack as being the expert on ODAG, any chance you could help with this?
