## Submitted Questions

• #### Splitting a Large Job DataStage into Smaller Jobs

What is the process to split the large datastage job into smaller jobs?

#### sai3689

• Nov 18th, 2015

Thanks Rohit

#### Rohit

• Oct 16th, 2015

If there is a job having more no of stages say around 20 stages. Then generate the output to an intermediate file like a dataset using the first 10 stages. After generating this use this intermediate dataset to comibine with the remaining stages and then generate the final file

• #### Convert Column Data into Row Data

Solve in Datastage Input: col1, col2, col3 ---------------- a, b, c d, e, f Output like Col --- a b c d e f

#### Ram

• Mar 23rd, 2016

Src --> colgen(Dummy) --> Pivot (type=Horizntal & derivaion col=col1,col2,col3) -->copy(drop dummy col) --> trg.

#### sai3689

• Oct 10th, 2015

In the properties Pivot type=horizontal Tell me which to take pivot column?

• #### Display with Commas

Solve these with suitable logics in datastage Input is like.. department_no, employee_name ---------------------------- 20, R 10, A 10, D 20, P 10, B 10, C 20, Q 20, S want result like department_no, employee_list -------------------------------- 10, A 10, A,B 10, A,B,C 10, ...

#### Shilpa Banerjee

• May 26th, 2016

Use Pivot enterprise stage and select vertical pivoting. Also perform sort in the stage using the partitioning as Hash. Take Dept_number as group by column and Employee_name as Pivot column and the output will be in the form of Dept_number|Employee list. For example:
10|A|B|C|D
20|P|Q|R|S

#### Vijayashree HN

• May 11th, 2016

First sort the columns Department_no and employee_name in ascending order. Then use the stage variables. current=department_no result= If current=prev then result:,:employee_name else employee_n...

• #### Cumulative Sum Calculation

Solve with steps in datastage employee_id, salary -------------------------- 10, 1000 20, 2000 30, 3000 40, 5000 Display as employee_id, salary, cumulative_sum ----------------------------------- 10, 1000, 1000 20, 2000, 3000 30, 3000, 6000 40, 5000, 11000

#### varun negi

• Dec 8th, 2015

* You can have a single stage variable svSum with initial value = 0.
* And keep the derivation as svSum = svSum + Salary

#### dstage_user

• Nov 13th, 2015

You can use following steps :-
1. Create 2 stage variables
a. stgvar1=> salary+ stgvar2
b. stgvar2=> stgvar1
Note :- Set initial value of stgvar2=0.

Assign stgvar2 to cumulative_sum column.

• #### Display the Rank in asending & descending order for salary column

Can anybody solve this interview question in DataStage? My input is: EMPNO, DEPTNO, SALARY 1 A 100 2 A 4000 3 A 400 4 B 500 5 B 150 6 B 700 I need extra column "Rank" in the output is: EMPNO, DEPTNO, SALARY, RANK 1 A 100 3 2 A ...

#### surya

• Sep 16th, 2015

1. Source-->sort(dept,sal), partition (hash(dept))
2. Transformer(rank=inrownum (inrownum will be rownumber in a partition which is nothing but rank)

• #### Giving occurances

can anyone pls solve in datastage......... source column is.. A A B B B C C D ........want output columns(2 cols) as......... A 1 A 2 B 1 B 2 B 3 C 1 C 2 D 1 THANX IN ADVANCE.

#### Amulya Kumar Panda

• Jul 11th, 2016

Job flow sequential_file-> eggregate->sort->tranformer-> sequential

fast aggregate stage use count rows and add extra column seq_no
next stage sort -key source column sort order ascending.
tranformer stage use loop while ex-@ITERATION

#### Kumar

• Jul 8th, 2016

Seq file>transformer>Seq file
1.Mode of execution in transformer must be sequential and data must be sorted on col1
2. Transformer logic:
Stv1=Input column
Stv2=if Stv1=Stv3 Then Stv2+1 Else 1
Stv3=Stv1
3.Output of transformer
Col1,Col2
Input column,Stv2

• #### Single column with multiple rows should be converted into single row with commas in datastage

Can any one help.... I have single column in Source like..... a b c d want target as..... a,b,c,d thanks in advance

#### D Hema

• Apr 23rd, 2015

Use the stag variable in the transformer
StageVar=StageVar:,: input column name
go to the constraints :-Lastrow()
map that stage variable to the output column

#### jallu

• Apr 11th, 2015

By using pivot stage in datastage

• #### Single column with multiple rows should be converted into single row with commas

Can any one help.... Source is..... a b c d want target as..... a,b,c,d thanks in advance

#### preeti

• Aug 9th, 2017

You can create a other array with some size and insert data in single array from double array for more see the following code for(int i=0;i

Code
1. class TestClass{
2.           public static void main(String l[])
3.           {
4.                   int [][]a={{1},{2},{3},{4},{5}};
5.                   int a1[]=new int[6];
6.                   int k=0;
7.                   for(int i=0;i<a.length;i++)
8.                   {
9.                           for(int j=0;j<a[i].length;j++)
10.                           {
11.
12.
13.                                   a1[k]=a[i][j];
14.                                   k++;
15.                                 System.out.println(a[i][j]);
16.                           }
17.
18.                   }
19.
20.
21.
22.  for(int i=0;i<a1.length;i++)
23.  {
24.         System.out.print(a1[i]);
25.  }
26.           }
27.
28.  }

#### sai3689

• Sep 16th, 2015

Thanks Amulya Kumar

• #### convert multiple rows into single row

hi... i have sourse like name,city johny,newyork johny,mumbai johny,del want output like..... name,city1,city2,city3 johny,nework,mumbai,delhi ----------------------------------------- thanks in advance...

#### RAM

• Aug 27th, 2015

Without using Pivot stage..
Source --> COPY
COPY --> Head(2) -->Tail(1) --> File2
COPY --> Tail(1)-->File3
now merge all file1,file2,file3 --> Target.
Pls check it may work.

#### sai3689

• Apr 1st, 2015

Thank u abishek

• #### Convert multiple rows into columns in datastage

hi i have sourse like name,city johny,newyork johny,mumbai johny,del want output like name,city1,city2,city3 johny,nework,mumbai,delhi thanks in advance..

#### Sharali Sharna

• Nov 24th, 2015

You can use Pivot Enterprise stage to achive this.

#### Abhinandan

• Nov 20th, 2015

Use 2 stage variable in transformer
Stagevar: if Stagevar1=sno then marks:",":Stagevar Else marks
Stagevar1: sno

map stageVar to marks