Datastage Real time scenario

Source table
In source table data like this
but I want traget table like this
name count
A 1
A 2
B 1
B 2
B 3
C 1
C 2
D 1

pls any one one solve this........

Questions by amulas   answers by amulas

Showing Answers 1 - 15 of 15 Answers


  • May 4th, 2012

We can do that in different ways.
1st one is using loop variables in transformation.
2nd is using stage variables and without loop variables.

1)Write SQL and take the count of each character like a--->2,b--->3,c--->2,d--->1
2)In the transformer stage for each character loop is repeated and lteraion>=count of character.
3)For example a ,loop repeated and data will be a 1 for first iteration and a 2 for second iteration.
4)We will get the final output

  Was this answer useful?  Yes


  • May 11th, 2012

First can u take stage variable ex: linkvalu=1 ---StgV1
and then take one more stage variable If StgV1=1 then 1+1 else StgV1-------StgV2

in the transformer stage output link u can create one more column that sequence no in that column derivation call StgV2

  Was this answer useful?  Yes


  • Oct 21st, 2012

first count the rows using transformer stage (stage variables)stgvar ===dslink source,,,stgvar 1===count the rows
stgvar 3====concatenate stgvar stgvar1 you will get the result


  • Dec 29th, 2012

1)Sequential file->Take Sequential File and Import The flat file which consist of (AABBBCCD)data.
2)Transform Stage-->Take Transformer Stage and create two Stage variables

In StageVariable1 Write the below condition
If StageVariable2=InputcolumnName Then StageVariable1+1 Else 1

In StageVariable2 put InputColumnName

3)Create one user define column using transformer Stage Say as "Count",Drag the Stagevariable2 to UserDefined Column

4)Link to output Stage (Sequential,Dataset..u r choice)

Complile and run


  • May 30th, 2013

This we can do by using transfromer.take 2 stage variables.

sv2: if sv1=iputcol then sv2+1 else 1
sv1 : inputcol
in derivation
inputcol:sv2 | outputcol

  Was this answer useful?  Yes


  • Feb 24th, 2015

We can use Aggregator stage and use the option Count rows(group by on the column name) connect the column to the output you ll get the counts of the rows like:
Name CountRows
A 2
B 3
C 2
D 1
then use Transformer and write a Loop While condition as @iteration <= your Column name(in this case Count Rows)
then append a new column to ur output and name it as @iteration from input side and change the name of the column at output end as Count.

  Was this answer useful?  Yes


  • Apr 2nd, 2015

Use sort stage in sort cluster key change coloum true.
then transformer stage create stage variable
If DSLink5.keyChange=1 Then DSLink5.keyChange Else StageVar+1
then map the sagevar to CountRow column

  Was this answer useful?  Yes

sambit kumar rout

  • Jul 1st, 2015

Use a filter stage to direct the output of the sequential file to 4 links based on the value either A or B or C or D. At the end of every output link, use a transformer stage. Output the value of first column, i.e A or B or C or D and use @INROWNUM value as the output of second column. Finally use funnel to combine all the data coming from the 4 links.

  Was this answer useful?  Yes

Rajasekhar Reddy

  • Jul 13th, 2015

By using Transformer....Create 3-Stage variable like Cur,Val,Pre after
Stagevariable-1 >> Cur=InputColumn
Stagevariable-2 >> Val If Cur=Pre then Val+1 Else 1
Stagevariable-3 >>Pre=Cur
Drag the Val into Derivation Part

  Was this answer useful?  Yes


  • Aug 10th, 2016

Ans: First take input as a sequential file
next take sort stage[column change=true]
after take t/r stage write condition create one stage variable
like that [if (key change=1) then 1 else sv+1]

  Was this answer useful?  Yes


  • Sep 12th, 2017

Ans- First take one sequential file at input
Now make a 4 node configuration file
Take a transformer and set partitioning type = hash
Now in transformer add extra column and set its derivation as system variable @ inrownum
Now we all knows that @inrownum gives row number as per the number of partiotion
so we have 4 partitions with same key value so it will give output like

  Was this answer useful?  Yes

Ruchi Gautami

  • Dec 30th, 2019

Take two stage variables Svar1 and Svar2.
Condition for Svar1:
if Svar2 =InputcolumnName Then Svar1+1 Else 1

  Was this answer useful?  Yes


  • Dec 8th, 2020

You can solve this problem by using two way
1. using a transformer
2. using aggregator and transformer
By using 1st method you can create 2 stage variable
i.e Stagevar1 and Stagevar2
In StageVar1 write a condition If stagevar2=column_name then Stagevar1+1 else 1 ;
in Stagevar2 called the input column
called stagevar1 stagevariable in output derivation you will get accepted output

  Was this answer useful?  Yes


  • Jul 10th, 2021

Sort Stage---KeyChange column true
Transfor- in advance tab set mode-Sequence
Create one stage Variable: Stagevar= if KeyChangecol=1 then 1 else Stagevar+1
Create on extracolumn in output give Stagevar=Newcolname

  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