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.
concat
Moderators: chulett, rschirm, roy
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), " ", "-")
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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.![Laughing :lol:](./images/smilies/icon_lol.gif)
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.
![Laughing :lol:](./images/smilies/icon_lol.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
The DelimitedRange routine builds up the range 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)
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)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Not according to the OP:ray.wurlod wrote:This is more complex as it seems. In case 2, there is a missing value, so two delimiters are required.
In all cases, it seems like there should be a single dash between any non-null fields.adams06 wrote:2. OUTPUT TWO I NEED TO FORMAT TO D - F WHICH I HAVE DONE IT BY USING "CHANGE FUNCTION"
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers