Help on long string with a delimeter
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 41
- Joined: Thu Oct 18, 2007 10:16 pm
- Location: Bangalore
- Contact:
Help on long string with a delimeter
Hi all
The requirement, is like..
I have about 300 input columns which inturn needs to be concatenated using '|' as the delimeter.
Eg: "Case number=':value in COL1|"Case Type=":value in COL2 so on COL300.
The second part of the requirement is , if the value between 2 delimeters exceeds more than 20 characters, add another delimeter at the end of the 20th character in addition to the delimeter at the end of the column.
Eg : Case number=1235732|7583|Case Type=Normal Cas|e without number|..
Any help is highly appreciated .
The requirement, is like..
I have about 300 input columns which inturn needs to be concatenated using '|' as the delimeter.
Eg: "Case number=':value in COL1|"Case Type=":value in COL2 so on COL300.
The second part of the requirement is , if the value between 2 delimeters exceeds more than 20 characters, add another delimeter at the end of the 20th character in addition to the delimeter at the end of the column.
Eg : Case number=1235732|7583|Case Type=Normal Cas|e without number|..
Any help is highly appreciated .
Cheers
Baksh
Baksh
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
Re: Help on long string with a delimeter
Tricky. Will there always be an '=' character? If so maybe you could use that to identify where your values are.
Could there be an '=' character anywhere else other than after the value name? Could the value name be more than 20 characters long? If the answer to either of these questions is "yes" then I think you have an unsolvable problem. So long as they are both "no", you can just find the = signs and then work backwards to the preceding '|' character to get your value name, and then work forwards from the '=' until you meet another '=' character and then work back from that to the preceding '|' and that's where the value ends.
Could there be an '=' character anywhere else other than after the value name? Could the value name be more than 20 characters long? If the answer to either of these questions is "yes" then I think you have an unsolvable problem. So long as they are both "no", you can just find the = signs and then work backwards to the preceding '|' character to get your value name, and then work forwards from the '=' until you meet another '=' character and then work back from that to the preceding '|' and that's where the value ends.
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
-
- Participant
- Posts: 41
- Joined: Thu Oct 18, 2007 10:16 pm
- Location: Bangalore
- Contact:
well.. the "Case Name=", "Case Number=" are all constants..its like
I'll have to take the literal " Case Name=" and pass teh case name column as the value for the literal.
As of now, what i've done is, i've concatenated all the 300 hundred literals and the columns with | as the delimeter, so now i have a long string with | as the delimeter.. what I need now is the way to find if the literal and the value exceeds the length of 20, if it does, i need to add a delimeter at the end of the 20th character.
I'll have to take the literal " Case Name=" and pass teh case name column as the value for the literal.
As of now, what i've done is, i've concatenated all the 300 hundred literals and the columns with | as the delimeter, so now i have a long string with | as the delimeter.. what I need now is the way to find if the literal and the value exceeds the length of 20, if it does, i need to add a delimeter at the end of the 20th character.
Cheers
Baksh
Baksh
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Fold() with Convert() in a basic transform can help. But as I won't advise you to use Basic Transformer, unless you have too long strings to convert or string of unknown length, you should the string more than 20 character long using substring "[]" with concatanation, that too before concatanating all 300 columns to make the task a bit easier.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.![Wink :wink:](./images/smilies/icon_wink.gif)
Genius may have its limitations, but stupidity is not thus handicapped.
![Wink :wink:](./images/smilies/icon_wink.gif)
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 41
- Joined: Thu Oct 18, 2007 10:16 pm
- Location: Bangalore
- Contact:
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 41
- Joined: Thu Oct 18, 2007 10:16 pm
- Location: Bangalore
- Contact:
well.. to answer this question, yes the incoming source is a oracle table, which has about 300 columns.
Here's what i have to be more clearer.
Case Name=DSLink1.Col1|Case Number=DSLink1.Col2|Case Person=DSLink1.Col3|Case Validity=DSLink1.Col4.. so on and so forth till Col300
where Case Name etc is the literal and DSLink.. is the value within the column.
Now, the outstanding issue is Literal+Value, if greater than 20 chars add another delimeter.
Here's what i have to be more clearer.
Case Name=DSLink1.Col1|Case Number=DSLink1.Col2|Case Person=DSLink1.Col3|Case Validity=DSLink1.Col4.. so on and so forth till Col300
where Case Name etc is the literal and DSLink.. is the value within the column.
Now, the outstanding issue is Literal+Value, if greater than 20 chars add another delimeter.
Cheers
Baksh
Baksh
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 41
- Joined: Thu Oct 18, 2007 10:16 pm
- Location: Bangalore
- Contact:
-
- Participant
- Posts: 41
- Joined: Thu Oct 18, 2007 10:16 pm
- Location: Bangalore
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Participant
- Posts: 46
- Joined: Tue Jul 20, 2010 1:26 pm
- Location: USA
- Contact:
Use Transformer and in its derivation for column use the way you want the output to be------
"Case No=":COL1:"|":"Case No=":COL2:"|":"Case No=":COL3:"|": ................................:"Case No=":COL300"
This way you have 300 rows in input and one in output
And as you want to break if the number of characters is more than 20 you need to use the if-then-else block as under:
"Col1=":
If Len(Col1) < 15
Then
Col1
Else (SubString(Col1,1,15):"|":SubString(Col2,16,Len(Col1)))
Probably if it does not works try using basic transformer in yours job
"Case No=":COL1:"|":"Case No=":COL2:"|":"Case No=":COL3:"|": ................................:"Case No=":COL300"
This way you have 300 rows in input and one in output
And as you want to break if the number of characters is more than 20 you need to use the if-then-else block as under:
"Col1=":
If Len(Col1) < 15
Then
Col1
Else (SubString(Col1,1,15):"|":SubString(Col2,16,Len(Col1)))
Probably if it does not works try using basic transformer in yours job