Exercise 9: How to Convert Column to Row

Exercise 9: How to Convert Column to Row and achieve target below?

Source
CITY,NAME
BNG,JOY
BNG,LABNITA
CHE,SRINIVAS
CHE,JOYDEEP
CHE,SHRUTHI
PUNE,PRIYA

Target
CITY,NAME
BNG,JOY,LABNITA
CHE,SRINIVAS,JOYDEEP,SHRUTHI
PUNE,PRIYA

Let's Chat: Though I have covered approx 2-3 scenarios that how to convert column to row. This scenario is too basic so, I am covering it in exercise.

Theory: Yes, you are thinking right, first we need to sort the data then we have to concatenate name until the next City arrived then we need to group records and select max(name) based upon city.

Practically

Step 1: Drag and Drop your source and target tables.

Step 2: 
Sorter
Sort the data based upon City.

CITY (Key Port)

Step 3:
Expression

CITY_COMPARE_FLAG (Variable Port)= IIF(CITY=OLD_CITY, 1, 0)

CITY_COMPARE (Variable Port) = IIF(CITY=OLD_CITY, OLD_CITY, CITY)

OLD_CITY (Variable Port) = CITY_COMPARE

NAME_CONCAT (Variable Port) = IIF(CITY_COMPARE_FLAG=1, OLD_CONCAT||','||NAME, NAME)

OLD_CONCAT (Variable Port) = NAME_CONCAT

OUT_NAME (Output Port) = NAME_CONCAT

Step 4
Aggregator
Connect CITY and OUT_NAME column from expression to Aggregator and create one more port

NAME (Output Port) = MAX(OUT_NAME)

Check GROUP port for CITY

Step 5
Connect NAME and CITY from Aggregator to Target.

BINGO!!!

 Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com

Scenario 20: How to insert same group of data in single column.

Scenario 20: How to insert same group of data in single column.

Source
ID,NAME,DEPTNO
100,JOHN,10
200,WALTER,20
300,GIBSON,30
400,KERRY,20
500,PHILIP,10

Target
DEPT10,DEPT20,DEPT30
JOHN,WALTER,GIBSON
PHILIP,KERRY,

Theory: It's quite challenging.... Hmmmm For achieving target above we need to treat each unique deptno as one column then we need to join all three columns to create a row.

Practically:

Step 1: Drag and drop your source and target to mapping designer.

Step 2:
Router
Create Three groups in Router
DEPT10 : DEPTNO=10
DEPT20 : DEPTNO=20
DEPT30 : DEPTNO=30

Step 3:
Expression
Create three Expression Transformation and connect each group of Router to one Expression Transformation. Let's say... EXPRESSION 1, EXPRESSION 2, EXPRESSION 3

Now, we need to crate an ID for each and every record based upon which we will join these columns and create a row.

EXPRESSION 1

ID (Variable Port) = OLD_ID + 1
OLD_ID (Variable Port) = ID
OUT_ID (Output Port) =  ID

EXPRESSION 2

ID (Variable Port) = OLD_ID + 1
OLD_ID (Variable Port) = ID
OUT_ID (Output  Port) =  ID

EXPRESSION 3

ID (Variable Port) = OLD_ID + 1
OLD_ID (Variable Port) = ID
OUT_ID (Output  Port) =  ID

Now we have three pipeline which we need to join so, (n-1)2 (Where n=pipeline) = 2 Joiner Transformation Required
As we are joining pipelines which are originated from same source qualifier so as per the rule we need to pass sorted input for both Joiner Transformation. So, let's create Sorter First

Step 4
Sorter
Connect Name and OUT_ID column from EXPRESSION 2 to Sorter.

OUT_ID (Key Port)

Step 5:
Joiner
Connect Name and OUT_ID from expression 1 and Sorter
Joiner Properties needs to be set in Properties Tab and Condition Tab
Sorted Input (Checked)
Join Type: Full Outer
Join Condition= OUT_ID=OUT_ID

Step 6
Sorter
Connect Name and OUT_ID column from EXPRESSION 3 to sorter.

OUT_ID (Key Port)

Step 7:
Joiner
Connect Name and OUT_ID from Joiner 1 and Sorter
Joiner Properties needs to be set in Properties Tab and Condition Tab
Sorted Input (Checked)
Join Type: Full Outer
Join Condition= OUT_ID=OUT_ID

Step 8
Now you will have total three name column will be there in your Joiner Transformation. Connect each name to target column.

BINGO!!!

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com

Scenario 19: How to populate date dimension through PL/SQL code by using stored procedure transformation.

Scenario 19: How to populate date dimension through PL/SQL code by using stored procedure transformation.

Let's Chat: Populating date dimension table can be done with multiple approaches according to requirements. Here I have taken a Basic one. In which I am taking a date and populating date dimension for complete year. Suppose date is coming '02-Jun-81' so, date dimension would be populated for complete year 1981. Starting from 01-Jan-81 to 31-Dec-81. If any date belongs to 1981 is coming once again in year then discard that date means populate date dimension table only for new calendar year.

Theory: It's too easy with help of procedure and same is used mostly in real time as well.

Date_Dimension Table Structure:
DATEKEY                                 NUMBER 
FULLDATE                                DATE 
DAYOFWEEK                           NUMBER
DAYNAMEOFWEEK                VARCHAR2(15)
DAYOFYEAR                             NUMBER
WEEKDAYWEEKEND            VARCHAR2(15)
WEEKOFYEAR                           NUMBER
MONTHNAME                            VARCHAR2(15)
MONTHOFYEAR                        NUMBER
QTR                                              NUMBER 
CAL_YEAR                              NUMBER

A Procedure Code which will populate Date Dimension.

create or replace PROCEDURE DATE_DIM
(in_date in date)
as
date_count number;
start_date date;
end_date date;
max_date_key number;
new_date_key number;
curr_date date;
Begin
--Check Whether Date Dimension table has been already populated for coming date?
-- If count is 0 then there is no data for coming date.
select count(*) into date_count
from date_dimension
where fulldate=in_date;

--Set Start_Date to first date of year. Ex: (01/01/2013)
select trunc(in_date, 'YYYY')into start_date from dual;

--Set end_date to Last date of year. Ex: (31/12/2013)
select add_months(trunc(in_date,'YYYY'), 12)into end_date from dual;

--Find the last generated DATE_KEY value
select max(datekey)into max_date_key from date_dimension;

--If last generated key value is null then assign 1 else assign last generated key value + 1 to new_date_key
select nvl2(max_date_key,max_date_key+1,1)into new_date_key from dual;

--For Loop set current date as Start Date
curr_date := start_date;

--If date count=0 means we don't have any data for particular year
if date_count = 0
then
--If condition is TURE then Start the Loop and insert record starting from 1st day to last day of year.

While curr_date < end_date loop
insert into date_dimension(datekey,FULLDATE,DATE_DAY,DAYOFWEEK, 
            DAYNAMEOFWEEK,DAYOFYEAR,WEEKDAYWEEKEND,WEEKOFYEAR,
            MONTHNAME,MONTHOFYEAR, qtr, cal_year)
select new_date_key,curr_date, TO_CHAR(curr_date,'DD'),
        TO_CHAR(curr_date,'D'),TO_CHAR(curr_date, 'Day'),
        TO_CHAR(curr_date, 'DDD'), 
        DECODE(TO_CHAR(curr_date, 'D'),  1,'Weekend',
                                      7,'Weekend',
                                      'Weekday'),
        TO_CHAR(curr_date, 'iw') ,TO_CHAR(curr_date, 'Month') ,
        TO_CHAR(curr_date, 'mm'),TO_CHAR(curr_date, 'q') ,
        TO_CHAR(curr_date, 'yyyy') from dual;

 --Generating new id for DATEKEY column       
new_date_key:= new_date_key + 1;

--Increment date by one for next date. 
curr_date := curr_date + 1;
END LOOP; 
end if;
End;

(Above code is tested and executed successfully. Feel free to mail me if you found any error)

Yahoo!!! Everything done by procedure, Now we need to create a simple mapping.

Step 1: We need to create a dummy source. Create a .txt target file with one column with date datatype.

Drag and Drop your source in Mapping Designer.

Step 2:

Stored Procedure Transformation.

Create Stored procedure Transformation.

Set In_Date port as output port as well. 
Connect Hiredate of EMP Table (Source) to In_Date port of Stored Procedure.

Step 3

Connect your Date_In port to Target.

We are done friends, create session and workflow and run it.

BINGO!!!

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.

hpandey.harsh@gmail.com