Join the data of two tables which does not have common columns

Description:

Lets assume I have two source files as shown below:

SOURCE 1		SOURCE 2
EmpNo Name 100 Sivan 200 Uday 300 Chandu 400 Satish		Sal Location 2000 Noida 3000 Banglore 1800 Hyderabad 1500 Chennai

I would like to load the target a shown below:

SNo	Name	Sal	Location
100	Sivan	2000	Noida
200	Uday	3000	Banglore
300	Chandu	1800	Hyderabad
400	Satish	1500	Chennai

Interview questions on Lookup transformatioin?

Q. What is a lookup transformation?

Ans: The Lookup transformation is passive transformation. Lookup is a transformation to look up data in a flat file or a relational table, view, or synonym.

There are two types of Lookups in Powercenter Designer, namely:

1. Connected Lookup
2. Unconnected Lookup .

Q. What are the tasks of a lookup transformation?

How to return muliple values using Unconnected lookup?

Today we discuss how to return muliple values using Unconnected lookup. First of all, the UnConnected lookup has one return port and returns one column from each row. If the interviewer asked the above scenario, follow the below solution.

• Let us assume EMP will be source table and DEPT will be LOOKUP table.
• Create a target table T_UNCONN_LKP in target designer. Table should contain all ports of EMP table plus DNAME and LOC.
• Go to the Mapping Designer and create the mapping with source, target, lookup and expression transformations as shown below:

Interview Questions on Aggregator Transformation

Q. What is aggregator transformation?

Ans: The Aggregator transformation allows us to perform aggregate calculations, such as averages and sums. Unlike expression transformation (performs calculations on a row-by-row basis), an Aggregator transformation allows us to perform calculations on groups.

RXVV7UF8ZQ6P

Q. What are the different types of aggregate functions?

Ans: The transformation language includes the following aggregate functions:

• AVG, COUNT , MAX, MIN, SUM
• FIRST, LAST
• MEDIAN, PERCENTILE, STDDEV, VARIANCE
• Single Level Aggregate Function: MAX(SAL)
• Nested Aggregate Function: MAX( COUNT( ITEM ))

Q. What is Nested Aggregate Functions?

Why the Sorter transformation is an active transformation?

Actually the Sorter transformation helps to sort the data in ascending or descending order. And hence the number of input rows to the Sorter transformation remains the same as the output rows.

But the Sorter transformation has an option "Distinct", using this option we can remove duplicate records. The number of records can changed, if the source contains duplicate rows. So the Sorter transformation is an active transformation.

How Union Transformation is Active transformation ?

Generally, the Union transformation returns same number of rows that passed to the transformation. For example; if source is table 1 contains 10 rows and Table 2 contain 5 rows then the Union transformation behaves like a UNION ALL in SQL and returns 1 records. That is the number of records unchanged which means it is a Passive transformation.

But in the Union transformation properties we have one option IS ACTIVE, when we enable this option it follow active mode. otherwise it will follow Passive mode.

How to filter out rows with null values?

To filter out rows containing null values or spaces, use the ISNULL and IS_SPACES functions to test the value of the port. For example, if we want to filter out rows that contain NULLs in the FIRST_NAME port, use the following condition:

IIF(ISNULL(FIRST_NAME),FALSE,TRUE)

This condition states that if the FIRST_NAME port is NULL, the return value is FALSE and the row should be discarded. Otherwise, the row passes through to the next transformation.