How to split one record into several records

[ambasta]

Hi All,
I am writing from dataset to Oracle.From Dataset Col.A[char(20)] and Col.B[char(30)] is coming.I need to populate Col.X using Col.A and col.B,but Col.X is having datatype as Char(2) only.Logic is... if Col.B is having data of more than 2 character then populate 2-2 char one by one.It is difficult to explain so,i am giving the following example to make my requirement clear.
If Source is like this...

ColA ColB

2846789NYXH75005C AXAMG1

VINNAGATEST000001 CS

Target should store it as

ColA Col .X

2846789NYXH75005C AX

2846789NYXH75005C AM

2846789NYXH75005C G1

VINNAGATEST000001 CS

Please help me and let me know if you want any more detail .


Thanks in Advance.

[ray.wurlod]

Assuming there is a maximum number of values for the two-character codes, separate them into separate columns (probably in a Modify or Transformer stage using substring techniques), then use a Pivot stage to generate a row for each.

[ambasta]

Thanks Ray,
I cannot create any additional column.what i need to do is...split one record and populate it as several records.I am not aware of pivot stage.can you plz explaim me a bit...

Thanks.

[talk2shaanc]

Step 1. I will use basic transformer.
Step2. Assuming you have only two columns (column-A and column-B). I will pass both the columns to a routine.
Step3. Function of routine:

Code: Select all

 a. loop through column-B , read 2 bytes every time and create a result something like below.
              Alogorithm: Lets have two variable  RtnColA and RtnColB
                   Read 2 byte and loop till end of string and perform below derivation for every loop. You can have a counter or something like that
                 RtnColA=RtnColA  :char(13): column-A
                 RtnColB= RtnColB :char(13): 2-Byte[i]

                b. In the end the routine should return a string something like
                       2846789NYXH75005C :char(13): 2846789NYXH75005C : char(13): 2846789NYXH75005C : char(13): VINNAGATEST000001 : '|' : AX : char(13):  AM : char(13):  G1 : char(13):  CS 

Step4: In transformer output link side.
O/Pcolumn-A = Routine Result, substring first portion before pipe (|)

O/Pcolumn-A = Routine Result, substring second portion after pipe (|).


Note: 1. char(13) is carriage return
2. The algorithm may not be 100% accurate, you may have to add checks like, if column-B is all spaces..something may be required for first
and last record.
3. Even if you have more than 2 column. I will pass a concatenated value of all columns as first parameter to routine and second parameter would be column-B

[DSguru2B]

Note: 1. char(13) is carriage return

char(13):char(10) is the CR for dos.

[ambasta]

Thanks Shantanu for your precious Input.but i cannot use server job and in Parallel job there is no Basic Transformer.

[talk2shaanc]

Note: 1. char(13) is carriage return

char(13):char(10) is the CR for dos.

Thanks, for correcting me...actually, i didn't notice the OS given...

ambasta, I was talking about PX job only. In PX you should ideally have Basic transformer

[ray.wurlod]

I cannot create any additional column.

You CAN create as many additional columns in your job as you require. Columns are link properties. These extra columns may exist only on the link between your Transformer stage and your Pivot stage.

[ambasta]

Thanks Ray,
Can you please elaborate... how to do that in transformer stage??I am having very urgent requirement.can you please help me??

Thanks

[ray.wurlod]

Etiquette Note
We don't do urgent here. It is an all volunteer site, we post as and when we can. And then only if we've something to contribute. If you need urgent, then sign up for premium service with your support provider, and learn the true cost of urgent.
Or, as the old saying goes, lack of planning on your part does not constitute an emergency on my part.

Create column definitions - or, to be totally professional - a table definition for the intermediate record schema.

For example, FirstCode, SecondCode, ThirdCode, up to as many as you'll ever need. Make them Char(2) and nullable.

[samsuf2002]

u can use this code in the transformer if it fits ur requirement

Declare stage variables as follows:
StageVar =DSLink3.No
StageVar1 =StageVar<> StageVar1
StageVar2 =StageVar
StageVar3 =DSLink3.Type:',':DSLink3.Name1:',': DSLink3.Name2
StageVar4 =IF StageVar2=1 then DSLink3.No:',': StageVar5 else Trim(StageVar4):',':Trim(StageVar5)

Output has two column with the below derivation:
No =DSLink3.No
Output =StageVar4
Check for the field length of the Output Column to be suficient to hold the concatenated data

Remove Duplicate Stage:
Pass the rows through a Remove Duplicate Stage and give the key as NO and select Last in the property Duplicate to Retain

Tested With:
Code:
1|A|AA|W
1|B|BB|N
1|C|CC|Y
1|D|DD|Z
2|A|AA|W
2|B|BB|N
2|C|CC|Y


Output:
Code:
1,A,AA,W,B,BB,N,C,CC,Y,D,DD,Z
2,A,AA,W,B,BB,N,C,CC,Y











OR







StageVar=DSLink3.No
StageVar2=StageVar<> StageVar1
StageVar1=StageVar
StageVar5=DSLink3.Type:',':DSLink3.Name1:',': DSLink3.Name2
StageVar4=IF StageVar2=1 then DSLink3.No:',': StageVar5 else Trim(StageVar4):',':Trim(StageVar5)

[ray.wurlod]

That's a nice vertical pivot, samsuf2002, but the question required a horizontal pivot.