topic Using concat() in Load Script in App Development

Using concat() in Load Script

Susan_DS — Mon, 13 Jun 2022 18:53:30 GMT

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!

Re: Using concat() in Load Script

rwunderlich — Wed, 15 Jun 2022 04:28:37 GMT

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

Re: Using concat() in Load Script

Susan_DS — Fri, 17 Jun 2022 20:14:51 GMT

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