Split one row to multiple rows
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 10
- Joined: Sat Dec 25, 2010 11:35 pm
- Location: Bangalore
- Contact:
Split one row to multiple rows
Hi,
I am calling the routine in the transformer the output of the routine is
a:12,b:123,c:12345,d:9 ..........
The Job design is : Source Table --> Transformer --> Hash File
Finally once the job is successfully run the out put of the hash file is
ColumnA , Column B
JOBNAME a:12,b:123,c:12345,d:9 ..........
Now i want to chage the output of the hash file to
COLUMNA , COLUMN B
JOBNAME , a:12
JOBNAME , b:123
JOBNAME , c:12345
JOBNAME , d:9
Can you guys please let me know how to solve this issue.
I am calling the routine in the transformer the output of the routine is
a:12,b:123,c:12345,d:9 ..........
The Job design is : Source Table --> Transformer --> Hash File
Finally once the job is successfully run the out put of the hash file is
ColumnA , Column B
JOBNAME a:12,b:123,c:12345,d:9 ..........
Now i want to chage the output of the hash file to
COLUMNA , COLUMN B
JOBNAME , a:12
JOBNAME , b:123
JOBNAME , c:12345
JOBNAME , d:9
Can you guys please let me know how to solve this issue.
-
- Premium Member
- Posts: 258
- Joined: Tue Jul 04, 2006 10:35 pm
- Location: Toronto
Re: Split one row to multiple rows
You can use a pivot stage to this.
in the derivation tab give Col1,Col2,Col3.
in the derivation tab give Col1,Col2,Col3.
You Can use soem thing like
Job1:
Source Table-->Tranformer-->Sequencialfile(not hash file)
Job2:
Sequential file -->PIvot stage -->hashfile
You can even combine Job1 and 2 in a single job.
In you sequential file if you have something like a:1,b:12,c:123 ...
and if you know the max number of fields you can use
col1,col2,col3,..col20 for the derivation in pivot stage.
I am not sure how this works for indefinite number of columns.
Job1:
Source Table-->Tranformer-->Sequencialfile(not hash file)
Job2:
Sequential file -->PIvot stage -->hashfile
You can even combine Job1 and 2 in a single job.
In you sequential file if you have something like a:1,b:12,c:123 ...
and if you know the max number of fields you can use
col1,col2,col3,..col20 for the derivation in pivot stage.
I am not sure how this works for indefinite number of columns.
Hari
-
- Premium Member
- Posts: 10
- Joined: Sat Dec 25, 2010 11:35 pm
- Location: Bangalore
- Contact:
When you go into the Output tab/Columns tab of the hashed file you will see a column called 'Type', double click it, from the drop down chose 'MV'. The 'Normalize On' option, previously greyed out, should now be availbe. Choose your column that you set 'MV' on from that drop down as well. Hit view data. Your data should now be normalized.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 10
- Joined: Sat Dec 25, 2010 11:35 pm
- Location: Bangalore
- Contact:
Thanks a lot.. It worked :DDSguru2B wrote:When you go into the Output tab/Columns tab of the hashed file you will see a column called 'Type', double click it, from the drop down chose 'MV'. The 'Normalize On' option, previously greyed out, should now be availbe. Choose your column that you set 'MV' on from that drop down as well. Hit view data. Your data should now be normalized.
-
- Premium Member
- Posts: 10
- Joined: Sat Dec 25, 2010 11:35 pm
- Location: Bangalore
- Contact: