Consolidating Lines in a Table

[#1]

Hi Gurus,

I have a .qvd formatted this way:

HeaderID	LineNo	Comment
1	1	This is line 1 for header 1
1	2	More for header 1
2	1	This is header 2 line 1
2	2	Line 2
2	3	Line 3
2	4	Line 4

We would like to have it like this in a table:

HeaderID	Comment
1	This is line 1 for header 1 More for header 1
2	This is header 2 line 1 Line 2 Line 3 Line 4

I'm currently going the looping route, with no luck. Any suggestions/code is greatly appreciated.

[Miguel_Angel_Baeyens]

Hello,

I think that Concat() function may be useful here (untested):

LOAD HeaderID, If(HeaderID = Previous(HeaderID), Concat(Comment, ';')) AS Comment FROM File.qvd (qvd) ORDER BY HeaderID;

Hope that helps.

[johnw · Accepted solution]

Or do a group by HeaderID, and sort by LineNo inside of the concat() using the optional third parameter:

LOAD
HeaderID
,concat(Comment,'
',LineNo) as Comment
FROM File.qvd (qvd)
GROUP BY HeaderID
;

[#4]

Thank you both for your thoughts. I actually used a combo of both:

LOAD
HeaderID
,concat(Comment,'
',LineNo) as Comment
FROM File.qvd (qvd)
GROUP BY HeaderID ORDER BY HeaderID, LineNo
;

The LineNo in the concat function is key. Without it, the concat seems random. Thanks again!