
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Is there a function similar to SQL Stuff function
Loading data and want to move the sql stuff function I currently use out of the sql query and into qlik but I'm having a hard time trying to find which function would be similar.
I need to group my records by the Bill_ID, and each Bill_ID can have multiple lines due to multiple Codes. I use the SQL STUFF function to get all the codes into a comma separated list, and want to do this in Qlik Sense rather than SQL.
Here's an example of a SQL query with a stuff function.
SELECT Bill_ID,
min(FromDate) as MinDOS,
max(ToDate) as MaxDOS,
stuff(( select distinct ', ' + C.CodeA + '-' + C.CodeB as Code
from BillHeader C
INNER JOIN BillDetail D2 on C.Bill_ID = d2.Bill_ID
where B.PackageID = C.PackageID for xml path('')), 1, 1, '') as CodeList
FROM BillHeader B
GROUP BY Bill_ID
Can anyone tell me how I can roll this up in Qlik Sense, so I have 1 row for each Bill_ID, and a comma separated list of Codes ?
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can use the concat() function to make a comma delimited list. If using in a query, it does work like an aggregation function, so you will also need a GROUP BY statement.
.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
something like:
CodeList:
Load
Bill_ID,
Concat(Code, ', ') as CodeList
Resident CodeSource
group by Bill_ID;
Then if you want it all in one table you'll just need to join it back in or use a mapping load for better performance with large volumes.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can use the concat() function to make a comma delimited list. If using in a query, it does work like an aggregation function, so you will also need a GROUP BY statement.
.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
something like:
CodeList:
Load
Bill_ID,
Concat(Code, ', ') as CodeList
Resident CodeSource
group by Bill_ID;
Then if you want it all in one table you'll just need to join it back in or use a mapping load for better performance with large volumes.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks, that did the trick.
