Logic for one row to multiple rows

[pandeesh]

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

[jwiles]

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

[pandeesh]

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

Thanks

[zulfi123786]

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.

[ajay.vaidyanathan]

Use a Column Generator and restrict it to 1 Column value with incremental integers (1,2,3,4,5....)

[pandeesh]

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

[SURA]

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]

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

[pandeesh]

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

[kmsekhar]

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

[pandeesh]

Thanks sekhar!i will try this

[zulfi123786]

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

[jwiles]

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,

[fmou]

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]

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.