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.

[dplr-rn]

Did you try concat function?
LOAD SalesOrderID, Concat(Comment) as FullComment Resident OrderComments Group By SalesOrderID

[Vegar]

Try this on your comment table. Note that I use chr(10) as separator. This will give you a line break after every comment.

Comments:
LOAD
SalesOrderLineId,
Concat(Comment, chr(10) as [Full comment]
FROM OrderLineCommentt.qvd (qvd)
GROUP BY SalesOrderLineId;

[suepenick]

so there are like between 6 to maybe 14 rows of comments from all the lines in the sales order

so the comment file is like this:  SO_number, row, Comment (30 char)

I need to loop and get all the rows worth of comments and put them all together, then eliminate all the excess spacing.

output as SO_number, Comment (one for each SO number)

I just don't know how to do the looping and keep getting the next one till the rows are done for that sales order.

[dplr-rn · Accepted solution]

You dont need a loop. You can just you use the suggestion mentioned by myself and vegar

LOAD SO_number, Concat(trim(Comment),'YOUR_SEPARTOR') as FullComment
Resident OrderComments
Group By SalesOrderID
order by row

[suepenick]

This is my script...it did concatenate the rows but they are all out of order. My script is below

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),'.') as FullComment

resident SOComments
Group by ord_no
Order by counter asc;

[dplr-rn]

Sorry. do order by Ord_no, counter

[suepenick]

SOComments:
LOAD
ord_no,
counter,
cmt

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

I added the order by ord_no asc - but it still didn't put the comments in order of the count.  Any Ideas.

//concatenate rows of comments for a sales order

OneCommentPerSO:
Load

ord_no,

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

resident SOComments
Group by ord_no
Order by ord_no asc, counter asc;

[dplr-rn]

Need to check your data for final details but below script (similar to suggestions) work as expected

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

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

RESULT

Order_No	Comment_Full
1	test..1-test..2-test..3
2	test..1-test..2-test..3

[suepenick]

my script is just like that...after staring at the comments that were concatenated, it looks like it is sorting the rows off comments in alphabetical of what is in the comment rows, not the counter...?