for next or do loop to concatenate rows of comments for a sales order

[suepenick]

I have a sales order line comment file that allows a user to add several lines for each sales order line...as many as they want.  I want to take all the rows of comments for the sales order - no matter how many - and put them all in one large field so I can link it to a sales order no matter which line they pick they see every comment.

I have the file loaded and did a sort and count to simplify finding out how many rows of comment are for each sales order

but now how to I go thru the data and get every comment for a sales order and put them all in one field?

then go to the next sales order and do it again.

[suepenick]

change your comments to  Counter= 1  comment is ATest1,  Counter 2 comment is DTest2,  Counter 3 comment is BTest3 and so forth...see what happens

[dplr-rn]

Ah ok..
you need to specify sort in concat as well. see below
Concat(trim(Comment),'-',Count) as Comment_Full

[dplr-rn · Accepted solution]

data

TestConcat:
load * Inline [
Order_No, Count, Comment
1,3,'ATest1'
2,1,'test..1'
1,1,'DTest1'
2,3,'test..3'
1,2,'XATest1'
2,1,'test..2'
];

ConcatTable:
load Order_No,
Concat(trim(Comment),'-',Count) as Comment_Full
Resident TestConcat
group by Order_No
;

Order_No	Comment_Full
1	DTest1-XATest1-ATest1
2	test..1-test..2-test..3

[suepenick]

this i finally the correct solution.  it worked perfectly!

SOComments:
LOAD
ord_no,
counter,
cmt

FROM
\\qlikview\QlikView\Common\QVDData\Temp_SOComments.qvd
(qvd);

//concatenate rows of comments for a sales order

OneCommentPerSO:
Load

ord_no,

Concat(trim(cmt),'.',counter) as FullComment

resident SOComments
Group by ord_no
Order by ord_no asc;