Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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
Partner Ambassador/MVP
Partner Ambassador/MVP

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