topic SV:Concat and order problem in QlikView

Concat and order problem

alex59800 — Wed, 15 Dec 2010 11:40:26 GMT

Hi all

I use concat in my script to fit together several comments in a notepad organized like this

Order N° Notepad Line Comments

1 5 box has

1 10 been damaged by postman

2 5 Products X missing

2 10 when received

2 15 at post office

3 3 Product X received an

3 7 Not ordered

My script

Table1:

LOAD [Notepad Line],
[Order N°],
Comments;
ORDER BY [Notepad Line],[Order N°];

Table 2:

Load [Order N°],

concat(Comments,' ') as [Concat comments]

Resident Table1

Group by [Order N°];

Drop Table1

My point: the concat is workin well, but comments are not ordered in the right way. I would like the comment to be concat in the same order as Notepad Line. For example for Order 1:

Order N° Comments

1 box has been damaged by postman

and not

Order N° Comments

1 been damaged by postman box has (which is more like a jedi language 🙂 )

Thanks a lot for your help

SV:Concat and order problem

Wed, 15 Dec 2010 11:53:00 GMT

concat can take up to 3 values. Have swedish installation but translated something like:

concat(expression,delimiter,sortvalue)

so you would use something like:

concat(comments,' ', [Notepad Line]) as [Concat comments]

You might have to fiddle around if it isn't working. Know I had some troubles before getting it to work a while ago.

/Michael

Concat and order problem

hector — Wed, 15 Dec 2010 13:32:29 GMT

Hi, this is the final tweak for the script



T2:
Load
 [Order N°],
 concat(Comments,' ',[Notepad Line]) as [Concat comments]
Resident DATA
Group by [Order N°]
;

And the result will be

Order N°	Concat comments
1	box has been damaged by postman
2	Products X missing when received at post office
3	Product X received an Not ordered

Rgds