Is there a function similar to SQL Stuff function

[LisaDrummond]

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 ? 

[Nicole-Smith · Accepted solution]

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.

[Rodj · Accepted solution]

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.

[LisaDrummond]

Thanks, that did the trick.