expr 2? how it works?

[#1]

if(index(Code, ' '), concat(left(Test, 1) & '(' & len(Test) & ')', ' ', Row), concat(left(Test, 1) & '(' & len(Test) & ')', '-', Row)) as Test2

[JonnyPoole]

I think this came from another thread. I'll try to write out what the expression is doing but it won't make too much sense unless you know your data well:

If the value in the field Code for the current record has a space in it  (ie:  ' ' ) then create a space delimitted list of values which will look at  ALL the values from the field TEST (every row, although this can be controlled by GROUP BY) and format this string of values as follows:

a) first character of the value in the field Test

b) The character  '('

c) The number of characters in the value of the field Test

d) The character ')'

The order of this delimitted list is determined by the row field in ascending order.

Otherwise...

If the value in Code does NOT have a space in, do the same thin except the delimitter will be a '-'

I think If the values for Code and Test on each row were something like this :

Code       Test     Row

a b           A          1

abc          B          2

a b          CCC      4

abc          DEE      3   

You would get something like this

Code       Test          Test2

a b           A               A(1) B(1) D(3) C(3)

abc          B               A(1)-B(1)-D(3)-C(3)

a b          CCC           A(1) B(1) D(3) C(3)

abc          DEE          A(1)-B(1)-D(3)-C(3)