Datastage Scenario Based Questions with Answers

Datastage Scenario Based Questions with Answers

Datastage Scenario Based Questions with Answers:

Question1 : 

Input :
source,destination,km
hyd,bang,1000
delhi,chennai,1500
chennai,bang,600
bang,hyd,1000
bombay,pune,1000
bang,chennai,600

Output :
source,destination,km
hyd,bang,1000
delhi,chennai,1500
chennai,bang,600
bombay,pune,1000

see here hyd to banglore distance is 1000km
another rows is also banglore to hyd 1000km
so need to delete rows like this.

Solution :

Kindly find the solution for this problem.There are so many different ways. This is, one of the way to get the 

Source=>Sort=>RemoveDup=>Transformer=>Lookup=>Filter=>Target

Steps:

1. Read the Source Data.
2. Sort the Data on initial 2 columns.
3. Remove duplicate values (If Any) on initial 2 columns.

4. Pass 2 output links from transformer.
One Link-> Master Link
Col1=> Source
Col2=> Destination
Col3=> Distance

2nd Link => Refrence Link
Col1=> Destination
Col2=> Source
Col3=> LkpChk ( With Default Value = 1)

5. Lookup the master link with the reference link.
with Col1=Col1 and Col2=Col2

Output from lookup is
One Link-> Master Link
Col1=> Source
Col2=> Destination
Col3=> Distance
Col4=> LkpChk( If Lookup found =1 Else you will get Null)

6. Filter the data only for (LkpChk <> 1)

7. You will get your required values.

Related Topics:

Datastage Scenario Based Questions Part-1

Datastage Scenario Based Questions Part-2

Datastage Scenario Based Questions Part-3

Read more

Datastage Scenario Based Questions - Part 3

Datastage Scenario Based Questions - Part 3

1. Consider the following product types data as the source.

Product_id, product_type
------------------------
10,          video
10,          Audio
20,          Audio
30,          Audio
40,          Audio
50,          Audio
10,          Movie
20,          Movie
30,          Movie
40,          Movie
50,          Movie
60,          Movie

Assume that there are only 3 product types are available in the source. The source contains 12 records and you dont know how many products are available in each product type.

Q1. Create a job to select 9 products in such a way that 3 products should be selected from video, 3 products should be selected from Audio and the remaining 3 products should be selected from Movie.

Q2. In the above problem Q1, if the number of products in a particular product type are less than 3, then you wont get the total 9 records in the target table. For example, see the videos type in the source data. Now design a mapping in such way that even if the number of products in a particular product type are less than 3, then you have to get those less number of records from another product types. For example: If the number of products in videos are 1, then the reamaining 2 records should come from audios or movies. So, the total number of records in the target table should always be 9.

2. Create a job to convert column data into row data.
The source data looks like

col1, col2, col3
----------------
a,    b,    c
d,    e,    f

The target table data should look like

Col
---
a
b
c
d
e
f

3. Create a job to convert row data into column data.

The source data looks like

id, value
---------
10,  a
10,  b
10,  c
20,  d
20,  e
20,  f

The target table data should look like

id, col1, col2, col3
--------------------
10,  a,   b,     c
20,  d,   e,     f

Recommended Reading

More Scenarios Based Interview Questions on Datastage at
Datastage Scenario Based Questions - Part 1
Datastage Scenario Based Questions - Part 2

Read more

Datastage Scenario Based Questions - Part 2

Datastage Scenario Based Questions - Part 2

1. Consider the following employees data as source?

employee_id, salary
-------------------
10,          1000
20,          2000
30,          3000
40,          5000

Q1. Create a job to load the cumulative sum of salaries of employees into target table?
The target table data should look like as

employee_id, salary, cumulative_sum
-----------------------------------
10,          1000,   1000
20,          2000,   3000
30,          3000,   6000
40,          5000,   11000

Q2. Create a job to get the pervious row salary for the current row. If there is no pervious row exists for the current row, then the pervious row salary should be displayed as null.

The output should look like as

employee_id, salary, pre_row_salary
-----------------------------------
10,          1000,    Null
20,          2000,    1000
30,          3000,    2000
40,          5000,    3000

Q3. Create a job to get the next row salary for the current row. If there is no next row for the current row, then the next row salary should be displayed as null.

The output should look like as

employee_id, salary, next_row_salary
------------------------------------
10,          1000,       2000
20,          2000,       3000
30,          3000,       5000
40,          5000,       Null

Q4. Create a job to find the sum of salaries of all employees and this sum should repeat for all the rows.

The output should look like as

employee_id, salary, salary_sum
-------------------------------
10,          1000,   11000
20,          2000,   11000
30,          3000,   11000
40,          5000,   11000

2. Consider the following employees table as source

department_no, employee_name
----------------------------
20,            R
10,            A
10,            D
20,            P
10,            B
10,            C
20,            Q
20,            S

Q1. Create a job to load a target table with the following values from the above source?

department_no, employee_list
--------------------------------
10,            A
10,            A,B
10,            A,B,C
10,            A,B,C,D
20,            A,B,C,D,P
20,            A,B,C,D,P,Q
20,            A,B,C,D,P,Q,R
20,            A,B,C,D,P,Q,R,S

Q2. Create a job to load a target table with the following values from the above source?

department_no, employee_list
----------------------------
10,            A
10,            A,B
10,            A,B,C
10,            A,B,C,D
20,            P
20,            P,Q
20,            P,Q,R
20,            P,Q,R,S

Q3. Create a job to load a target table with the following values from the above source?

department_no, employee_names
-----------------------------
10,            A,B,C,D
20,            P,Q,R,S

Recommended Reading

More Scenarios Based Interview Questions on Datastage at
Datastage Scenario Based Questions - Part 3
Datastage Scenario Based Questions - Part 1

Read more

Datastage Scenario Based Questions and Answers for Freshers and Experienced

Datastage Scenario Based Questions and Answers for Freshers and Experienced

1. Create a job to load the first 3 records from a flat file into a target table?

2. Create a job to load the last 3 records from a flat file into a target table?

3. Create a job to load the first record from a flat file into one table A, the last record from a flat file into table B and the remaining records into table C?

4. Consider the following products data which contain duplicate records.

A
B
C
C
B
D
B

Answer the below questions

Q1. Create a job to load all unique products in one table and the duplicate rows in to another table.

The first table should contain the following output

A
D

The second target should contain the following output

B
B
B
C
C

Q2. Create a job to load each product once into one table and the remaining products which are duplicated into another table.

The first table should contain the following output

A
B
C
D

The second table should contain the following output

B
B
C

Recommended Reading

More Scenarios Based Interview Questions on Datastage at
Datastage Scenario Based Questions - Part 2
Datastage Scenario Based Questions - Part 3

Read more