topic Consolidating Lines in a Table in QlikView

Consolidating Lines in a Table

Thu, 16 Sep 2010 16:48:51 GMT

Hi Gurus,

I have a .qvd formatted this way:

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 — Thu, 16 Sep 2010 18:35:06 GMT

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 — Thu, 16 Sep 2010 19:29:02 GMT

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
;

Thu, 16 Sep 2010 22:19:27 GMT

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!