String Parsing

[ArndW]

Try 000

[sudharsanan]

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

[Sunshine2323]

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

[ArndW]

Sunshine has identified your problem and offerred a solution. Good luck!

[sudharsanan]

Thanks Sunshine and Arndw.. for your inputs.. i will try Sunshine's appraoch and will let know the result...

Thanks
Sudharsanan

[sudharsanan]

Thanks Sunshine and Arndw.. for your inputs.. i was able to get the Result...


Regards
Sudharsanan

[sudharsanan]

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

[ArndW]

Hi Sunshine,
...
StageVar1=EREPLACE( DSLink3.Field1 ,"&",CHAR(10):StageVar1:",")
...

Try using:

StageVar1=EREPLACE( FIELD(DSLink3.Field1,',',2) ,"&",CHAR(10):StageVar1:",")

[sudharsanan]

Hi Arndw,

I used your logic for the stage variable and the result is

Field1Field2

1A=B
1,C=D
1,E=F
1,G=H
2


Thanks
Sudharsanan

[srekant]

Hi,
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).

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.

[ArndW]

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:

Code: Select all

FIELD(DSLink3.Field1,',',1):',':EREPLACE(FIELD(DSLink3.Field1,',',2),'&',CHAR(10):FIELD(DSLink3.Field1,',',1):',')

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.

[varshanswamy]

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

[srekant]

In Transformer u have the substring function i.e [,],use this function to split the second column in the source i.e

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

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

Code: Select all

[b]columnname                        derivation                    Key               [/b]
Field1                      name of the first input column           yes
field2                       Field21,field22,field23,field24            no    

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

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

[Sunshine2323]

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

[varshanswamy]

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

In Transformer u have the substring function i.e [,],use this function to split the second column in the source i.e

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

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

Code: Select all

[b]columnname                        derivation                    Key               [/b]
Field1                      name of the first input column           yes
field2                       Field21,field22,field23,field24            no    

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

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