concat

[adams06]

FIELD1 FIELD2 FIELD3
A B C
D F
G
I NEED TO CONCATENATE THIS FIELD1,FIELD2,FIELD3 AND SEPERATE THEM WITH -
EXAMPLE OUTPUT
1. A - B - C
2. D -- F
3. G--
1. THE PROBLEM IS OUTPUT ONE IS IN THE CORRECT FORMAT
2. OUTPUT TWO I NEED TO FORMAT TO D - F WHICH I HAVE DONE IT BY USING "CHANGE FUNCTION"
3. IN OUTPUT THREE I NEED TO REPLACE -- WITH BLANK.

[kcbland]

Use spaces as a delimiter, then trim out duplicates and leading/trailing, then change to dashes. Try:

Code: Select all

CHANGE(TRIM(FIELD1:" ":FIELD2:" ":FIELD3), " ", "-")

[chulett]

If you'll always have a FIELD1, that simplifies things. In any case, the concatenation is the dash and the field in question, if the field is non-null. If FIELD1 can be null, then you need to track the first non-null field - that one doesn't get a dash. Any non-null fields after the first non-null gets both. Probably best to do it in a routine rather than attempt some convoluted if-then-else logic directly in the derivation.

Oh, and TYPING IN ALL CAPS is considered shouting.

Bah... type all that and Ken has the elegant solution already posted! Trust me to do it the hard way.

[ray.wurlod]

This is more complex as it seems. In case 2, there is a missing value, so two delimiters are required. This is where some dynamic array functions or a routine will be useful. You need to append empty values until each new item is found, and append that. With intervening delimiters.

Assuming that Field3 >= Field2 >= Field1 always, the follwing expression will come close.

Code: Select all

Ereplace(DelimitedRange(Field1,Field2) : DelimitedRange(Field2,Field3), Field2:Field2, Field2, 1, 1)

The DelimitedRange routine builds up the range with intervening delimiters.

Code: Select all

FUNCTION DelimitedRange(Arg1,Arg2)
$OPTIONS EXTRA.DELIM
* For simplicity, assume that both Arg1 and Arg2 are alphabetic characters
* and that Arg2 >= Arg1
Ans = ""
SeqArg1 = Seq(Arg1)
SeqArg2 = Seq(Arg2)
For N = SeqArg1 To SeqArg2
   Ans<-1> = Char(N)
Next N
Ans = Convert(@FM, "-", Ans)
RETURN(Ans)

[chulett]

This is more complex as it seems. In case 2, there is a missing value, so two delimiters are required.

Not according to the OP:

2. OUTPUT TWO I NEED TO FORMAT TO D - F WHICH I HAVE DONE IT BY USING "CHANGE FUNCTION"

In all cases, it seems like there should be a single dash between any non-null fields.