# Datastage real time scenario

I have source like this
a,b,c,1,2,3 ( All this in one column)
I wanna target following below
a,b,c,1 ( Ist row)
a,b,c,2 (2nd row)
a,b,c,3 (3rd row)

#### Vinod

• Oct 21st, 2011

This one is the most complicated that i had ever seen.

Basically it is inverse pivot functionality. but in order to perform inverse pivot functionality we need at-least 2 columns. So, i am going to generate that extra column with "1" in 6 rows.

Then Based on this i done the concatenation, after concatenation i used copy stage then from copy stage one link is going to remove duplicate stage and another one to lookup stage. in remove duplicate stage i collected the retain = last value (a,b,c,1,2,3), 1 . in lookup stage i combined both the data and used transformer stage to collect (a,b,c) then i made a concatenate with (a,b,c) with actual values(a,b,c,1,2,3) then the data will be {(a,b,c,a),(a,b,c,b),(a,b,c,c),(a,b,c,1),(a,b,c,2),(a,b,c,3)} now i specified a condition so that i can collect the specified requirement {(a,b,c,1),(a,b,c,2),(a,b,c,3)}

I can't post the screen shot's if u need further clarification Feel free to mail mee. I will send the screen shots..

ALL THE BEST !!
KEEP POSTING...

#### kumar

• Mar 23rd, 2012

We can do it using Transformer.Take 3 stage variables(s1,s2,s3),for s1 map the input column and for s2 we have to write the condition like if alpha(inputcolumn)= true then trim(s3:,:s1,,,) else s3:,:inputcolumn. for s3 also we have to write the condition like if alpha(inputcol) true then map s2 into s3 else map s3 to s3.

Input column---->S1
if alpha(inputcolumn)=true then trim(s3:,:s1) else trim(s3:,:inputcolumn)-------->s2
if alpha(inputcoumn)=true then s2 else s3------->s3
In constraint part we have to write the below condition
if alnum(s2)=true
In derivation part we have to map s2 to output column
I think it will work

#### poorna

• Mar 25th, 2012

source-->Transformer(use field function by taking stage variable like field(inputcolumn,,,1,3) and concatenate this field value aging with field ----> Pivot stage--->target.

#### rupam Profile Answers by rupam Questions by rupam

• Apr 17th, 2012

Source->TR->RD->Pivot->Target,by using these order of stages we can get required output.

Transformer:

we have to concatenate the values by using loop(we will get like a,b,c,1,2,3) after that we have to split it into separate fields using field function.
o/p is:c1 c2 c3 c4 c5 c6 c7
1 a
1 a b
1 a b c
1 a b c 1
1 a b c 1 2
1 a b c 1 2 3
(if we dont want this dummy column we can drop it here itself)

RemoveDuplicate:

put condition retain last

o/p is: c1 c2 c3 c4 c5 c6 c7
1 a b c 1 2 3

pivot;

in derivation of c5 column give c5,c6,c7

here we are converting columns into rows

o/p is: c2 c3 c4 c5 c6 c7
a b c 1
a b c 2
a b c 3

#### sreenu

• May 18th, 2012

Hi.,
we can achieve this by
source--->pivot-->Transformer --> target

we will get 6 columns from pivot stage(columns to rows) then
In transformer stage we have to concatenate the input columns
like col1:col2:col3:(here we can use stage variables and increment that value by one)

regards
sreenu

#### venkatasuresh

• Jun 12th, 2013

Source we have like this a,b,c,d,e,f here 6 columns

a,b,c,1,2,3
e,f,g,4,5,6 we need to get in target
like this a,b,c,1 and a,b,c,2 and a,b,c,3 and e,f,g,4 for that we have to use just three stages

source---------------->pivot------------------------>target

in pivot we need to implement this code

column,derivation
a,a
b,b
c,c
values,d,e,f and target file name this will work to get that result

#### Prabhakar Achyuta

• Jul 23rd, 2013

Input:
CUST_ID
----------
a,b,c,1,2,3

Output:
CUST_ID:
-------------
a,b,c,1
a,b,c,2
a,b,c,3

Solution:
We can solve it from below solution.

JOB flow: Transformer (3 ouyput links)----> Funnel---->Dataset

In Transformer: Use 3 stage variables,
in sv1: left(Cust_id,1,7)
in sv2: left(Cust_id,1,6)||Left(right(Cust_id,3),1)
in sv3: left(Cust_id,1,6)||Right(Cust_id,1)

Take 3 outputs from Transformer, map sv1 to output-1 column CUST_ID,
map sv2 to output-2 column CUST_ID,
map sv3 to output-3 column CUST_ID
Here one input record splits into 3 output records.

Now cature all three output records using Funnel stage.

#### sivaksa

• Aug 13th, 2013

Guys try simple way.

Source.....trans.....pivot...target

trans...we can use filed function and create new column

col1:filed(inputcol,,,4,1)
col2:filed(inputcol,,,5,1)
Col3:filed(inputcol,,,6,1)

And pivot put output col :col1,col2,col3

Then you can get output

#### Ruchir

• May 11th, 2015

You can code in below way also.I have coded this and tested..
Source-->Trans-->Target(dataset)
In the Transformer stage, go to looping condition and Add the below one
Loop While: @ITERATION<=3
Loop Derivation:-
LoopVar:- Field(I/p_Column_Name,,,1,3):,:@ITERATION
Then Map this to your O/P column named COL1
COL1--> LoopVar
You will get O/P is the same way as mentioned:-
a,b,c,1
a,b,c,2
a,b,c,3

#### Nish

• May 24th, 2015

It is better to use source ->xmfr-> pivot option, it is more generic and it will work in DS8.0 . Note that @ iteration is not available prior to DS 8.5.

• May 27th, 2015

take input as one columns and use field fun in transformer stage and divide string into columns after that do pivot stage then you will get output as above

input columns->transformer stage(use filed function)->pivot stage

#### Pankaj

• Jun 17th, 2015

This logic is far better................ great job.

#### bimaljsr@gmail.com

• Aug 10th, 2015

Source like this
a,b,c,1,2,3 ( All this in one column)

seq. file ---> tfm ---->(3 output link)--->funnel ---> output file

in tfm -
o/p lnk1- col1,col2,col3,col4
o/p lnk2 - col1,col2,col3,col5
o/p lnk3 - col1,col2,col3,col6

any doubt ?

#### Ankita Srivastava

• Aug 13th, 2015

seq file ----------> pivot ent stg ----------> trfrm stg ------------> funnel stg -----------> ds

#### tulasi

• Dec 6th, 2015

Input :
a
b
c
target:
a
bb
ccc
How can I get this. could anyone help me to do this......

#### Ram

• Mar 10th, 2016

Its simple.
Src (read a single field) --> Trans(Use filed function) --> Pivot Enterprise--> Trg.
It will work. Pls let me know if any concern.

#### Ram

• Mar 10th, 2016

Hi Tulasi,
This is for you..
Src -->Trns (use Function Str(inputcolumn,@inputrow) --> Trg
Thats all !

#### lalit

• Sep 12th, 2017

Its really simple
Take one sequential file which contains data a,b,c,1,2,3 ( First line column name=false)
Then take one copy stage with three output link and then copy a,b,c,1 at one output link
Then send these 3 output link to funnel and rename all op link column name as field1 ,field2 ,field3 ,field4 with same datatype
Now send that funnel o/p to another sequential file you will get an output like
a,b,c,1
a,b,c,2
a,b,c,3

Thats it
Thank you