String Parsing
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 64
- Joined: Fri Jul 16, 2004 7:53 am
Arndw,
Thanks for replying...I used your logic.. i changed CHAR(10) with the "*" and the delimit to 000.. the result i got is ..
Field1Field2
1B*1,C=D*1,E=F*1,G=H
2M*2,G=H
I also tried the Sunshine Logic of merging the input using a '|' in between the input.. then also i am not getting the correct output.. i think i am troubling you people with my questions.. sorry as i am new to Datastage.. just learning...
Thanks for the replies...
Regards
Sudharsanan
Thanks for replying...I used your logic.. i changed CHAR(10) with the "*" and the delimit to 000.. the result i got is ..
Field1Field2
1B*1,C=D*1,E=F*1,G=H
2M*2,G=H
I also tried the Sunshine Logic of merging the input using a '|' in between the input.. then also i am not getting the correct output.. i think i am troubling you people with my questions.. sorry as i am new to Datastage.. just learning...
Thanks for the replies...
Regards
Sudharsanan
-
- Charter Member
- Posts: 130
- Joined: Mon Sep 06, 2004 3:05 am
- Location: Dubai,UAE
String Parsing
Hi Sudharshan,
Are you reading the whole input as a single column?
When you do view data on the input field you should get
Field1
1,A=B&C=D&E=F
2,G=M
Then you have to use this single input column Field1 in both the derivations for the stage variables and you will get the required result. Don't use "," as the delimiter in your input file use "|" or something else
Are you reading the whole input as a single column?
When you do view data on the input field you should get
Field1
1,A=B&C=D&E=F
2,G=M
Then you have to use this single input column Field1 in both the derivations for the stage variables and you will get the required result. Don't use "," as the delimiter in your input file use "|" or something else
Warm Regards,
Amruta Bandekar
<b>If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut. </b>
--Albert Einstein
Amruta Bandekar
<b>If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut. </b>
--Albert Einstein
Sunshine has identified your problem and offerred a solution. Good luck!
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 64
- Joined: Fri Jul 16, 2004 7:53 am
-
- Participant
- Posts: 64
- Joined: Fri Jul 16, 2004 7:53 am
-
- Participant
- Posts: 64
- Joined: Fri Jul 16, 2004 7:53 am
Hi Sunshine,
I merged my input into one column..
When i see the sequential file VIEW DATA the Result was
1,A=B&C=D&E=F&G=H
2,L=M&G=H
I changed the Sequential file delimiter to 000
Then i used the same Stage variables
StageVar=FIELD(DSLink3.Field1,",",1)
StageVar1=EREPLACE( DSLink3.Field1 ,"&",CHAR(10):StageVar1:",")
The output that i got was this...
Field1Field2
11,A=B
1,C=D
1,E=F
1,G=H
2,L=M
1,G=H
The Result for First and last was not correct..
Please advice in solving this issue...
Thanks
Sudharsanan
I merged my input into one column..
When i see the sequential file VIEW DATA the Result was
1,A=B&C=D&E=F&G=H
2,L=M&G=H
I changed the Sequential file delimiter to 000
Then i used the same Stage variables
StageVar=FIELD(DSLink3.Field1,",",1)
StageVar1=EREPLACE( DSLink3.Field1 ,"&",CHAR(10):StageVar1:",")
The output that i got was this...
Field1Field2
11,A=B
1,C=D
1,E=F
1,G=H
2,L=M
1,G=H
The Result for First and last was not correct..
Please advice in solving this issue...
Thanks
Sudharsanan
Try using:sudharsanan wrote:Hi Sunshine,
...
StageVar1=EREPLACE( DSLink3.Field1 ,"&",CHAR(10):StageVar1:",")
...
StageVar1=EREPLACE( FIELD(DSLink3.Field1,',',2) ,"&",CHAR(10):StageVar1:",")
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 64
- Joined: Fri Jul 16, 2004 7:53 am
String parsing
Hi,
I am able to get output as required in DataStage Parallel Jobs using a pivot the design is as shown below
I/p is a sequential file and in Transformer(Xfm) i am spliting the second column of input into multiple columns using subset function and sending as an input to pivot stage which will do the trick for you.
I am able to get output as required in DataStage Parallel Jobs using a pivot the design is as shown below
Code: Select all
I/P------------>Xfm----------------->Pivot------------->o/p(DataSet).
Sree
sudharsanan,
I really don't wish to give you all the code now, it would seem that you are not really looking at the functions used. The string functions and operations are not that difficult if you try to analyze them. I wrote the code and submitted it for you - but I did expect you to try to understand why the line reads as it does instead of just plugging it into your job and coming back here when it didn't work.
Here is a variation that works:
The FIELD(DSLink3.Field1,',',1) returns the value of what is before the first comma in the string.
The EREPLACE replaces all occurences of '&' with <lf>{beforefirstcomma}',' in the FIELD(DSLink3.Field1,',',2) - which is what is after the comma.
If you have questions about how this works, create a routine and separate out the single steps with PRINT statements.
I really don't wish to give you all the code now, it would seem that you are not really looking at the functions used. The string functions and operations are not that difficult if you try to analyze them. I wrote the code and submitted it for you - but I did expect you to try to understand why the line reads as it does instead of just plugging it into your job and coming back here when it didn't work.
Here is a variation that works:
Code: Select all
FIELD(DSLink3.Field1,',',1):',':EREPLACE(FIELD(DSLink3.Field1,',',2),'&',CHAR(10):FIELD(DSLink3.Field1,',',1):',')
The EREPLACE replaces all occurences of '&' with <lf>{beforefirstcomma}',' in the FIELD(DSLink3.Field1,',',2) - which is what is after the comma.
If you have questions about how this works, create a routine and separate out the single steps with PRINT statements.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 48
- Joined: Thu Mar 11, 2004 10:32 pm
Hi I was the original participant and I have 2 seperate fields, and I need to read them as 2 seperate fields itself, and I need to implement the
solution for and input comma seperated file as follows
Field1, Field2
1, A=B&C=D&E=F&G=H
2, L=M&G=H
I need to write a job in datastage PX 7.1 where I coud'nt see the subset function to split this string which would be dynamic
and have it of the form. here also I have 2 fields, Could you please send me the solution using the pivot stage which you have mentioned.
Field1, Field2
1, A=B
1, C=D
1, E=F
1, G=H
2, L=M
2, G=H
solution for and input comma seperated file as follows
Field1, Field2
1, A=B&C=D&E=F&G=H
2, L=M&G=H
I need to write a job in datastage PX 7.1 where I coud'nt see the subset function to split this string which would be dynamic
and have it of the form. here also I have 2 fields, Could you please send me the solution using the pivot stage which you have mentioned.
Field1, Field2
1, A=B
1, C=D
1, E=F
1, G=H
2, L=M
2, G=H
Solution
In Transformer u have the substring function i.e [,],use this function to split the second column in the source i.e
The output of the transformer to the pivot stage(pivot stage provides horizantal data, that is ,columns with in a single row into many rows).
in the pivot stage outputs tab do the below
U will have the output as below
1 A=B
1 C=D
1 E=F
1 G=H
2 L=M
2 G=H
I hope this helps u out
Code: Select all
[b]Derivation[/b] [b]Columnname[/b]
Map as it is from input Field1
split the second column as below
DSLink3.FIELD2 [1,3] Field21
(DSLink3.FIELD2[5,7])[1,3] Field22
DSLink3.FIELD2 [9,11][1,3] Field23
DSLink3.FIELD2[13,15] Field24
in the pivot stage outputs tab do the below
Code: Select all
[b]columnname derivation Key [/b]
Field1 name of the first input column yes
field2 Field21,field22,field23,field24 no
1 A=B
1 C=D
1 E=F
1 G=H
2 L=M
2 G=H
I hope this helps u out
varshanswamy wrote:Hi I was the original participant and I have 2 seperate fields, and I need to read them as 2 seperate fields itself, and I need to implement the
solution for and input comma seperated file as follows
Field1, Field2
1, A=B&C=D&E=F&G=H
2, L=M&G=H
I need to write a job in datastage PX 7.1 where I coud'nt see the subset function to split this string which would be dynamic
and have it of the form. here also I have 2 fields, Could you please send me the solution using the pivot stage which you have mentioned.
Field1, Field2
1, A=B
1, C=D
1, E=F
1, G=H
2, L=M
2, G=H
Sree
-
- Charter Member
- Posts: 130
- Joined: Mon Sep 06, 2004 3:05 am
- Location: Dubai,UAE
String Parsing
Hi Varsha,
You can use the Convert function to convert all the "&" to ","
So after conversion your data will look as follows
1,A=B,C=D,E=F,G=H
2,L=M,G=H
Then you can use the pivot to pivot the data and get the result in the required format
1, A=B
1, C=D
1, E=F
1, G=H
2, L=M
2, G=H
You can use the Convert function to convert all the "&" to ","
So after conversion your data will look as follows
1,A=B,C=D,E=F,G=H
2,L=M,G=H
Then you can use the pivot to pivot the data and get the result in the required format
1, A=B
1, C=D
1, E=F
1, G=H
2, L=M
2, G=H
Warm Regards,
Amruta Bandekar
<b>If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut. </b>
--Albert Einstein
Amruta Bandekar
<b>If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut. </b>
--Albert Einstein
-
- Participant
- Posts: 48
- Joined: Thu Mar 11, 2004 10:32 pm
Re: Solution
I string is not a static one to be of the format
A=B&C=D&E=F, it can also be of the format
A=B1234&C=D+1&E=F, so I cant use what you have mentioned please could you help me on the same
A=B&C=D&E=F, it can also be of the format
A=B1234&C=D+1&E=F, so I cant use what you have mentioned please could you help me on the same
srekant wrote:In Transformer u have the substring function i.e [,],use this function to split the second column in the source i.e
The output of the transformer to the pivot stage(pivot stage provides horizantal data, that is ,columns with in a single row into many rows).Code: Select all
[b]Derivation[/b] [b]Columnname[/b] Map as it is from input Field1 split the second column as below DSLink3.FIELD2 [1,3] Field21 (DSLink3.FIELD2[5,7])[1,3] Field22 DSLink3.FIELD2 [9,11][1,3] Field23 DSLink3.FIELD2[13,15] Field24
in the pivot stage outputs tab do the below
U will have the output as belowCode: Select all
[b]columnname derivation Key [/b] Field1 name of the first input column yes field2 Field21,field22,field23,field24 no
1 A=B
1 C=D
1 E=F
1 G=H
2 L=M
2 G=H
I hope this helps u out
varshanswamy wrote:Hi I was the original participant and I have 2 seperate fields, and I need to read them as 2 seperate fields itself, and I need to implement the
solution for and input comma seperated file as follows
Field1, Field2
1, A=B&C=D&E=F&G=H
2, L=M&G=H
I need to write a job in datastage PX 7.1 where I coud'nt see the subset function to split this string which would be dynamic
and have it of the form. here also I have 2 fields, Could you please send me the solution using the pivot stage which you have mentioned.
Field1, Field2
1, A=B
1, C=D
1, E=F
1, G=H
2, L=M
2, G=H