Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.
Moderators: chulett , rschirm , roy
pandeesh
Premium Member
Posts: 1399 Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU
Post
by pandeesh » Thu Dec 08, 2011 11:01 am
hi,
My requirement is to Add a additional field with different values ,SO that one row become multiple rows.
if the input is like:
Then the output should be:
Code: Select all
A|B|C|D|1
A|B|C|D|2
A|B|C|D|3
A|B|C|D|4
A|B|C|D|5
Right now,i am taking 5 links from transformer and adding a column with values and finally combining using FUnnel.
I dont think this is the proper way.
Is there any smart way to achieve this in single transformer?
Thanks
pandeeswaran
jwiles
Premium Member
Posts: 1274 Joined: Sun Nov 14, 2004 8:50 pm
Contact:
Post
by jwiles » Thu Dec 08, 2011 11:58 am
That's pretty much the only option you have with a transformer prior to IS 8.5.
- james wiles
All generalizations are false, including this one - Mark Twain.
pandeesh
Premium Member
Posts: 1399 Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU
Post
by pandeesh » Thu Dec 08, 2011 12:06 pm
Thanks James!!
How we can handle this in 8.5 transformer?
Thanks
pandeeswaran
zulfi123786
Premium Member
Posts: 730 Joined: Tue Nov 04, 2008 10:14 am
Location: Bangalore
Post
by zulfi123786 » Thu Dec 08, 2011 1:22 pm
pandeesh wrote: h
Is there any smart way to achieve this in single transformer?
do you absolutely want to do this in transformer ?
The output is as good as Cartesian product so find the distinct values of the extra column and join it with the main stream that hold the other 4 column over a dummy value.
- Zulfi
ajay.vaidyanathan
Participant
Posts: 53 Joined: Fri Apr 18, 2008 8:13 am
Location: United States
Post
by ajay.vaidyanathan » Thu Dec 08, 2011 4:36 pm
Use a Column Generator and restrict it to 1 Column value with incremental integers (1,2,3,4,5....)
Regards
Ajay
pandeesh
Premium Member
Posts: 1399 Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU
Post
by pandeesh » Thu Dec 08, 2011 7:32 pm
I don't think it's possible with column generator since each record should assign to all the 5 values. So that a row becomes 5 rows.
Correct me if I am wrong
pandeeswaran
SURA
Premium Member
Posts: 1229 Joined: Sat Jul 14, 2007 5:16 am
Location: Sydney
Post
by SURA » Thu Dec 08, 2011 8:01 pm
Sorry bit of gap in my understanding!
1. As per you example you have 1 records , but need N numbers OR
2. like you have N numbers of records and need to add 1 coulumn to make it as unique? Or
3. You have N numbers of records and in that you have dups and make them also as unique you need this column?
DS User
pandeesh
Premium Member
Posts: 1399 Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU
Post
by pandeesh » Thu Dec 08, 2011 8:33 pm
Sura,
The first point (1) you have mentined is my requirement.
Finally, i am planning to do that using AWK in unix using the filter option in sequential file stage,,
I will try and let you know.
Thanks
pandeeswaran
pandeesh
Premium Member
Posts: 1399 Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU
Post
by pandeesh » Thu Dec 08, 2011 8:41 pm
zulfi123786 wrote: The output is as good as Cartesian product so find the distinct values of the extra column and join it with the main stream that hold the other 4 column over a dummy value.
yes..this will work.
I will try.
Thanks
pandeeswaran
kmsekhar
Premium Member
Posts: 58 Joined: Fri Apr 16, 2010 12:58 pm
Location: Chn
Post
by kmsekhar » Thu Dec 08, 2011 11:35 pm
Hi Pandeesh, try this option
lets assume you are geeting input as | delimiter with 4 columns
ex: Input Data
Link your I/P to Transformer stage then define a stage variable to hold the 4 column values.
Code: Select all
svRow-->Colum1:"|":Column2:"|":Column3:"|":Column4
Again define 5 stage variables:
Code: Select all
svVal1-->svRow:"|":"aa"
svVal2-->svRow:"|":"bb"
svVal3-->svRow:"|":"cc"
svVal4-->svRow:"|":"dd"
svVal5-->svRow:"|":"ee"
Then in the derivation define only one column
Code: Select all
OUTPUTCOLUMN-->svVal1:CHAR(10):svVal2:CHAR(10):svVal3:CHAR(10):svVal4:CHAR(10):svVal5
CHAR(10)--> ASCII code for newline
output will be:
Code: Select all
A|B|C|D|aa
A|B|C|D|bb
A|B|C|D|cc
A|B|C|D|dd
A|B|C|D|ee
pandeesh
Premium Member
Posts: 1399 Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU
Post
by pandeesh » Thu Dec 08, 2011 11:47 pm
Thanks sekhar!i will try this
pandeeswaran
zulfi123786
Premium Member
Posts: 730 Joined: Tue Nov 04, 2008 10:14 am
Location: Bangalore
Post
by zulfi123786 » Fri Dec 09, 2011 1:35 am
Cartesian Product will give you the benifit of unlimited distinct values where as in transformer you need to be sure of the number of distinct values for the last column
- Zulfi
jwiles
Premium Member
Posts: 1274 Joined: Sun Nov 14, 2004 8:50 pm
Contact:
Post
by jwiles » Fri Dec 09, 2011 5:56 am
OUTPUTCOLUMN-->svVal1:CHAR(10):svVal2:CHAR(10):svVal3:CHAR(10):svVal4:CHAR(10):svVal5
This will NOT be the same as 5 individual records. This will instead create a
single column within a
single record which contains a string of character data containing occasional linefeeds. I don't think that's what you're wanting.
In IS 8.5 and beyond, you can use the looping functionality of the transformer to easily do this (in fact, the transformer documentation shows how).
If you know C/C++, you can do this in a BuildOp as well.
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
fmou
Participant
Posts: 124 Joined: Sat May 28, 2011 9:48 pm
Post
by fmou » Wed Dec 14, 2011 11:37 am
Is there any smart way to achieve this in single transformer?
Do you
have to use transformer?
Pivot is exactly designed for such task. Check out
viewtopic.php?t=142442
kwwilliams
Participant
Posts: 437 Joined: Fri Oct 21, 2005 10:00 pm
Post
by kwwilliams » Thu Dec 15, 2011 8:10 pm
From your source add a column generator and create one additional column, Dummy Char(5) with a value of DUMMY.
Create a row generator with two columns, Dummy char(10) with a value of DUMMY and then another count integer with a value from 1-5.
Join the source data to the row generator data on on the DUMMY column. Then run this output through a copy stage and drop the DUMMY column.