Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
suepenick
Contributor

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

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.

 

Labels (3)
2 Solutions

Accepted Solutions
dilipranjith
Valued Contributor II

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

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
dilipranjith
Valued Contributor II

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

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_NoComment_Full
1DTest1-XATest1-ATest1
2test..1-test..2-test..3
13 Replies
dilipranjith
Valued Contributor II

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

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

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

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
Contributor

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

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.

 

dilipranjith
Valued Contributor II

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

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
Contributor

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

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;

 

dilipranjith
Valued Contributor II

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

Sorry. do order by Ord_no, counter
suepenick
Contributor

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

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;

dilipranjith
Valued Contributor II

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

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_NoComment_Full
1test..1-test..2-test..3
2test..1-test..2-test..3
suepenick
Contributor

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

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...?

Community Browser