How to convert records based on certian contents of records
Moderators: chulett, rschirm, roy
How to convert records based on certian contents of records
Hi All,
My requirement is to convert the data based on certain conditions.I am explaining the problem using an example.
If data is like 12345$ABC6789 then populate data as 12345A6789
if data is like 12$BCD3456789 the populate data as 12B3456789
if data is like 1234567$CDE89 the populate data as 1234567C89.
these four digits ($ABC,$BCD,$CDE) can be at any position in the source data.
Can anyone of you please help me on this...
Thanks in Advance..
My requirement is to convert the data based on certain conditions.I am explaining the problem using an example.
If data is like 12345$ABC6789 then populate data as 12345A6789
if data is like 12$BCD3456789 the populate data as 12B3456789
if data is like 1234567$CDE89 the populate data as 1234567C89.
these four digits ($ABC,$BCD,$CDE) can be at any position in the source data.
Can anyone of you please help me on this...
Thanks in Advance..
ambasta
Do this:
Get everything before the dollar sign, concatenate it to the first character of everything after the $ sign. Then finally get rid of all your alphabets and concatenate everything after the dollar sign. Pretty much what the above code is doing.
NOTE: If there are other characters, excluding $ sign, that will be present along with alphabets, add them to the list in the CONVERT() function.
Code: Select all
Field(in.Col, "$",1):Field(in.Col,"$",2)[1,1]:Field(CONVERT('ABCDEFGHIJKLMNOPQRSTUVWXYZ','',Upcase(in.Col)),"$",2)
NOTE: If there are other characters, excluding $ sign, that will be present along with alphabets, add them to the list in the CONVERT() function.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Thanks a lot for your kind suggession..actually i am not sure why we are taking substring[1,1].Will you be kind enough to explain the logic using any example.Thanks in advance..DSguru2B wrote:Do this:Get everything before the dollar sign, concatenate it to the first character of everything after the $ sign. Then finally get rid of all your alphabets and concatenate everything after the dollar sign. Pretty much what the above code is doing.Code: Select all
Field(in.Col, "$",1):Field(in.Col,"$",2)[1,1]:Field(CONVERT('ABCDEFGHIJKLMNOPQRSTUVWXYZ','',Upcase(in.Col)),"$",2)
NOTE: If there are other characters, excluding $ sign, that will be present along with alphabets, add them to the list in the CONVERT() function.
ambasta
Re: How to convert records based on certian contents of reco
Look at your final data. It contains the first alphabet right after the $ sign. Thats what the substring is for.ambasta wrote: If data is like 12345$ABC6789 then populate data as 12345A6789
if data is like 12$BCD3456789 the populate data as 12B3456789
if data is like 1234567$CDE89 the populate data as 1234567C89.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Ambasta, there is no need to quote entire messages when replying.
DSGuru2B's logic is succinct:
Take the whole string before the first $ sign
Append the single character after the first $ sign
Remove alpha characters from the remainder and append.
I might suggest changing that a bit to read
since your delimiting string is always 4 chars long.
And for efficiency it might be best to assign a stage variable to the output of Field(in.Col,"$",2).
DSGuru2B's logic is succinct:
Take the whole string before the first $ sign
Append the single character after the first $ sign
Remove alpha characters from the remainder and append.
I might suggest changing that a bit to read
Code: Select all
Field(in.Col, "$",1):Field(in.Col,"$",2)[1,1]:Field(in.Col,"$",2)[4,9999]
And for efficiency it might be best to assign a stage variable to the output of Field(in.Col,"$",2).
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You've missed the point. You don't need to treat them separately, because they follow a common pattern, namely nnnnnn$aaaannnnn. As was described - documented - in the response by DSguru2B, the expression takes:
- everything to the left of the $ character {Field(x, "$", 1, 1)}
the first character of (everything to the right of the $ character) {Left(Field(x, "$", 2, 1), 1)} - which he gave as Field(x, "$", 2, 1)[1,1]
then removes all the alphabetic characters from (everything to the right of the $ character) [Convert("ABCDEFGHIJKLMNOPQRSTUVWXYZ", "", Upcase(Field(x, "$", 2, 1))}
finally concatenating the three intermediate results to form the desired answer
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: