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)
Thanks in advance.

Questions by amulas   answers by amulas

Showing Answers 1 - 13 of 13 Answers

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

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

Prabhakar Achyuta

  • Jul 23rd, 2013
 

Very simple..please go through below.

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.

  Was this answer useful?  Yes

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     

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

prasad

  • 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

  Was this answer useful?  Yes

Pankaj

  • Jun 17th, 2015
 

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

  Was this answer useful?  Yes

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 ?

  Was this answer useful?  Yes

Ankita Srivastava

  • Aug 13th, 2015
 

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

  Was this answer useful?  Yes

tulasi

  • Dec 6th, 2015
 

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

  Was this answer useful?  Yes

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.

  Was this answer useful?  Yes

Ram

  • Mar 10th, 2016
 

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

  Was this answer useful?  Yes

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
a,b,c,2 at second output link
a,b,c,3 at third 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

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions