DSXchange

Posted: **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:

Code: Select all

A|B|C|D

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

Posted: **Thu Dec 08, 2011 11:58 am**

That's pretty much the only option you have with a transformer prior to IS 8.5.

Posted: **Thu Dec 08, 2011 12:06 pm**

Thanks James!!
How we can handle this in 8.5 transformer?

Thanks

Posted: **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.

Posted: **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....)

Posted: **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

Posted: **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

Posted: **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

Posted: **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

Posted: **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

Code: Select all

A|B|C|D --> input row 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

Posted: **Thu Dec 08, 2011 11:47 pm**

Thanks sekhar!i will try this

Posted: **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

Posted: **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,

Posted: **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

Posted: **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.

DSXchange

Logic for one row to multiple rows

Logic for one row to multiple rows

Re: Logic for one row to multiple rows

Logic for one row to multiple rows

Re: Logic for one row to multiple rows

Re: Logic for one row to multiple rows

Re: Logic for one row to multiple rows

Re: Logic for one row to multiple rows