Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Susan_DS
Partner - Contributor III
Partner - Contributor III

Using concat() in Load Script

Hi, Experts,

I have an app with a data model in a snowflake schema. Customers are linked to products ordered using a cross table. (There are many other tables in the data model; we won't go into that.)

My audience would like to see a table chart in the app that displays one row per customer, with all the products that customer has ordered in a single cell in the table chart.

I know I can use an expression in the table chart like concat(ProductName, ', ') to create a comma delimited list. However, in my real-world app, there are more than 250,000 customers, many with multiple product orders, and using the concat function in the table chart is bogging down performance.

I am running into an issue using concat() in the load script, since the ProductName and CustomerID are in two separate tables.

The attached qvf contains a very simple example of 7 customers ordering 10 different products. The Excel document contains the dummy data and the "desired output" tab shows what I'm trying to replicate in the Qlik app. 

I would appreciate any help you can offer to resolve this! Thanks!

 

 

Labels (1)
2 Replies
rwunderlich

To do this in the script you'll have to create at least a temporary association between CustomerObjId and ProductName. You could do that by

1. Join ProductName to the CrossTable.

2. Load CustomerObjid, concat(ProductName) resident CrossTable group by CustomerObjId.

3. Drop ProductName from CrossTable. 

Also see https://qlikviewcookbook.com/2020/03/creating-temporary-script-associations/ for tips on creating temporary script associations. 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Susan_DS
Partner - Contributor III
Partner - Contributor III
Author

Thanks very much, Rob. Appreciate your insights and this very informative post! 

I applied your proposed solution above, but I think I may have missed something in the Load script. The table chart is now displaying all 10 products in a concatenated string for every customer. My goal is to only show the concatenated string of product name values specific to each customer in the table chart.

Would you take a look at below and let me know where I am missing the step? 

//Dimension
[Products]:
LOAD
[ObjectID] AS [ProductObjID],
[ProductID],
[ProductName],
[Price]
FROM [lib://AttachedFiles/TestData.xlsx]
(ooxml, embedded labels, table is Products);

//For connecting fact table and dimensions
[CrossTable]:
// Unqualify [ObjectID];
LOAD
[ObjectID],
[CustomerObjID],
[ProductObjID]
FROM [lib://AttachedFiles/TestData.xlsx]
(ooxml, embedded labels, table is [Cross-Table]);

//Fact table
[Customers]:
LOAD
[ObjectID] AS [CustomerObjID],
[CustomerID],
[CustomerName],
[CustomerAddress1],
[CustomerCity],
[CustomerStateProv],
APPLYMAP( '__cityKey2GeoPoint', APPLYMAP( '__cityName2Key', LOWER([CustomerCity])), '-') AS [Customers.CustomerCity_GeoInfo]
FROM [lib://AttachedFiles/TestData.xlsx]
(ooxml, embedded labels, table is Customers);

Join([CrossTable])
Load [ProductName]
Resident [Products]
;

Load CustomerObjID,
concat(distinct ProductName, ', ') as ConcatProductName
Resident CrossTable
Group by CustomerObjID
;

Drop field [ProductName] from [CrossTable]
;