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

Exercise 8: Implementing SCD1

Exercise 8: How to achieve Target below?

Source

part_number  business_unit  business_group

01yp          unassigned            undefined

02yp          unassigned            undefined

03yp          unassigned            undefined

Target

part_key   part_number  business_unit  business_group

      1           01yp          unassigned            undefined

      2           02yp          HSBC                 undefined

      3           03yp          unassigned           undefined

Note: There are some other process which also runs on same target table and update business unit according to their need. so, in target you can see for part_number 02yp business unit is HSBC. It's not processed by Informatica it's processed by some other application.

Requirement Specification:

Case 1: New Part in Source insert into Target.

Case 2: If business_unit changed in source (Other than unassigned) then only update the business unit in target.

Theory/Analysis: It's a simple requirement of SCD 1 only one thing is need to keep in mind that while updating simple comparison of Target Business_Unit and Source Business_Unit will not work here because some other process has modified this column with value. We need to include one more condition while comparing...

Practically

Step 1

Drag and Drop Source and two Target instance to mapping designer.

Step 2

Lookup

Create Lookup and follow steps below...

A) Drag and drop part_number from source Qualifier to Lookup.

B) Lookup-->Right Click-->Edit-->Condition Tab

     Lookup Table Column          Operator         Transformation Port

                 part_number                =              part_number1

Step 3

Expression

Take Business_unit, Part Key, Part_Number column from lookup and all three columns from source qualifier.

Create Two Ports

New_Record=IIF(ISNULL(PART_NUMBER_TGT), 1, 0)

Upd_Record=IIF(NOT ISNULL(PART_NUMBER_TGT) AND (LOWER(BUSINESS_UNIT)!= 'unassigned' AND (BUSINESS_UNIT_TGT != BUSINESS_UNIT)), 1, 0)

Step 4

Router

Take Part_key column of Lookup and all source column including New_Record and Upd_Record to Router.

Create Two Group

New_Rec=New_Record = 1

Upd_Rec=Upd_Record = 1

Step 5

Connect New_Record group column to first target instance.

Step 6

Sequence Generator

Connect NEXTVAL port to Part_key column of first target instance.

Step 7

Update Strategy

Connect Part_key and Business_unit column from Upd_Rec group of Router to Update Strategy.

B)

Right Click-->Edit-->Properties Tab

Update Strategy Expression = DD_UPDATE

Step 8

connect part_key and business_unit port to second target instance.

Note 1: At the session level "Treat Source rows as: DATA DRIVEN" must be set.

Note 2: At target level you must have a primary key. If not available at database level then create it Informatica level.

Limitation: Solution above will work only when unassigned value is coming in Source Business_unit Column if actual value of Business_unit is missing .

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

Exercise 7: How to create and use Pipeline Lookup or How to create Active Lookup?

Exercise 7: How to Achieve Target below

(Source)
ins_id,premium_year,premium_Amount
101,2010,NULL
101,2011,4500
101,2012,NULL
102,2010,NULL
102,2011,6000
102,2012,NULL

Note: NULL is just a representation. There is no value over there.

(Target)
ins_id,premium_year,premium_Amount
101 2010 4500
101 2011 4500
101 2012 4500
102 2010 6000
102 2011 6000
102 2012 6000

Let's Chat: I know this exercise we have already solved in Exercise 6 CASE 2. Here I am taking same scenario and will resolved it though Active pipeline lookup.

What is Pipeline Lookup?
A pipeline lookup is used do lookup upon an application source that is not a relational table or flat file.
A Source Qualifier has been used as a lookup table for Pipeline Lookup.
A Source Qualifier which has been used as a lookup table remains abandon in mapping.

What is the Use of Pipeline Lookup?
To improve performance when processing relational or flat file lookup sources, create a pipeline Lookup transformation instead of a relational or flat file Lookup Transformation. 

Pipeline Lookup can be created in Connected or Unconnected mode.


Theory/Analysis:
After analyzing source and target we can easily conclude that the premium amount value of each id will be repeating for each and every row of that id but here value is coming in second row not in first row then it's quite little bit challenging and for this we will create a Active Pipeline Lookup to take all rows of ID and Pass only that value where Premium Amount is NOT NULL. If you want to create Active Lookup then simply one Check box you have to click while creating lookup.

Practically:

Step 1:
Drag and Drop your source twice and Target once.

Step 2:
Lookup

While creating Select Source Qualifier and Select check box Return all Values on Multiple Match (See Picture Below)

Connect Ins_ID, Premium_Year from Source qualifier (Upon which you are not doing Lookup) to Lookup Transformation.
Condition Tab
Ins_Id = Ins_Id 1

Step 3
Filter
Connect Ins_ID, Premium_Year (Column of Source) and Premium_Amount Column of Lookup from Lookup to Filter Transformation.

Filter Condition: NOT ISNULL(Premium_Amount)

Step 4
Connect Your Columns from Filter to Target.

Step 5:
Give the Source connection or Location/File_Name for both source qualifier.
No need to give Source connection or Location/File_Name for Lookup Table.

Note: Your Second Source and Source Qualifier upon which you are doing Pipeline Lookup will be abandon in mapping no need to do anything with them. Your mapping will look like Image below...

BINGO!!!

Note: You can also achieve this by doing Active Lookup directly  upon your source. Approach explained above is just a representation that how to create Pipeline Lookup.

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

Exercise 6: How to replace Null with Value

Exercise 6: How to replace NULL with value and achieve target below?

CASE 1
(Source)
ins_id,premium_year,premium_Amount
101,2010,4500
101,2011,NULL
101,2012,NULL
102,2010,6000
102,2011,NULL
102,2012,NULL

Note: NULL is just a representation. There is no value over there.

(Target)
ins_id,premium_year,premium_Amount
101 2010 4500
101 2011 4500
101 2012 4500
102 2010 6000
102 2011 6000
102 2012 6000

Theory/Analysis: After analyzing source and target we can easily conclude that the premium amount value of each id will be repeating for each and every row of that id So if, some how we can store the Premium_Amount first value for each ID and pass it for each and every row then we are done.

Practically:
Step 1
Drag and Drop your source and Target Instance

Step 2
Expression
Create Three ports

PREMIUM_COMPARE (Variable Port) = IIF(ISNULL(premium_Amount),OLD_PREMIUM,premium_Amount)

OLD_PREMIUM (Variale Port) = PREMIUM_COMPARE

O_PREMIUM_AMOUNT (Output Port) = PREMIUM_COMPARE

Step 3
Connect your target from expression. Only one thing needs to take care while connecting Premium_Amount column connect it through O_PREMIUM_AMOUNT port.

That's it... Wow!!! It's was this much easy completed in only three steps.

BINGO!!!

CASE 2
(Source)
ins_id,premium_year,premium_Amount
101,2010,NULL
101,2011,4500
101,2012,NULL
102,2010,NULL
102,2011,6000
102,2012,NULL

Note: NULL is just a representation. There is no value over there.

(Target)
ins_id,premium_year,premium_Amount
101 2010 4500
101 2011 4500
101 2012 4500
102 2010 6000
102 2011 6000
102 2012 6000

Theory/Analysis: After analyzing source and target we can easily conclude that the premium amount value of each id will be repeating for each and every row of that id but here value is coming in second row not in first row then it's quite little bit challenging and our CASE 1 solution does not work here. We have to think about some other logic.

Practically:

Step 1
Drag and Drop your source and Target Instance

Step 2
Aggregator
Connect ins_id and Premium_Amount from Source Qualifier
Create One Port

O_Premium_Amount (Output Port) = MAX(Premium_Amount)

Step 3
Sorter
Connect Ins_Id and Premium_Year from Source Qualifier

Select Ins_Id as key port.

Ins_ID (key)

(Clarification: We need sorter here as we are going to join two pipeline of same source qualifier and for this we need to pass sorted input for Joiner Transformation.)

Step 4
Joiner
Connect Ins_Id and Premium_Year from Sorter and Ins_Id, O_Premium_Amount from Aggregator

Set Properties below...

Condition Tab

Ins_Id 1 = Ins_Id

Join Type: Normal

Step 5
Connect Ins_Id and Premium_Year and Premium_Amount from Joiner 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 

Exercise 5: How to find sum of negative and sum of positive integer separately.

Exercise 5: How to find sum of negative and sum of positive integer separately.

Ex: 
Source 
Account_Balance
500
600
-300
-200
400
-100

Target
Total_Account_Balance
1500
-600

Theory/Analysis: After analyzing source and target we can conclude that we have to find the sum for positive and negative number separately. Here only thing we need to identify, the positive and negative integer then finding sum is not a big task. That's it. Identifying integer is also very easy :-)

Step 1
Drag and drop one instance of source and two instance of target.

Step 2
Router
Create one Group
Negative_Number=SUBSTR(TO_CHAR(Account_Balance), 1, 1)='-'

Step 3
Aggregator
Create two Aggregator Transformation

Connect Aggregator1 from Negative_Number Group of Router Transformation
Create one port
Total_Balance (Output Port) = Sum(Account_Balance1)

Connect Aggregator2 from Default Group of Router Transformation
Create one port
Total_Balance (Output Port) = Sum(Account_Balance2)

Step 4:
Connect your both target instance from separate Aggregator Transformation.

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 

Exercise 4: How to load top 2 salaries for each department without using Rank Transformation and SQL Queries in Source Qualifier?

Exercise 4: How to load top 2 salaries for each department without using Rank Transformation and SQL queries in Source Qualifier.

Analysis/Theory: After analyzing requirement we can conclude that after sorting the data based upon salary in descending order, if we can pass only two records for each group of department then we are done. That's it!!! Is in't it too easy :-)

Practically

Step 1:
Drag and drop one source and three instance of target table.

Step 2: 
Sorter
Sort the data based upon SAL.

Select SAL as key port and Descending order

Step 3
Router
Create three groups.

Dept10_Data =  Dept=10
Dept20_Data =  Dept=20
Dept30_Data =  Dept=30

Step 4
Sequence Generator
Create Three sequence Generator Transformation
(Change END VALUE =2, START VALUE=1 and Check Cycle) 
By this way your mapping will always give you your expected output.

This step we need to assign an ID for each and every group of records. (If you don't want to use Sequence generator use Expression and build a logic to assign an unique id for each and every record but in that case also we need three different Expression transformation :-)

Step 5
Filter
Create three different Filter Transformation

Connect from each group of Router Transformation to different Filter Transformation like below
Dept10_Data To Filter 1
Dept20_Data To Filter 2
Dept30_Data To Filter 3

Develop Filter condition = NEXTVAL <=2 in all Filter Transformation.

Step 6
Connect all three instance of target from different Filter Transformation.

 Filter 1 To Target Instance 1
 Filter 2 To Target Instance 2
 Filter 3 To Target Instance 3

Yahoo!!! it was pretty easy. Keep Practicing and Learning.

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 

Exercise 3: How to remove duplicates? If source is a flat file

Exercise 3: How to remove duplicates? If source is a flat file

OR

How to send distinct records to one target and duplicates to others

Theory/Analysis: When records are repeating then we are saying table have duplicates. We can achieve this by comparing current record and previous record. If it's matched then record would be flagged as duplicate record otherwise it's a distinct record. Yes, we need to sort the data first before comparing current and previous record.

Practically:

Step 1:

Drag and Drop your source/target to mapping designer.

Step 2: 

Sorter

Sort the data based upon port for which you are finding duplicate.

EMPNO     Key (Check)

Step 3:

Expression

Create three ports.

EMPNO_COMPARE (Variable Port) = IIF(EMPNO=OLD_REC, 1, 0)

OLD_EMPNO  (Variable Port ) = EMPNO

DUP_RECORD (Variable Port ) = EMPNO_COMPARE

Step 4:

Router

Create two Groups

New_Record: DUP_RECORD = 0

Dup_Record: DUP_RECORD = 1

Step 5:

Connect New_Record group to distinct target and Dup_Record group ports to duplicate target.

BINGO!!!

NOTE: Above solution will eliminate only column level duplicates means if only EMPNO is repeating then only it will filter it out to duplicate target table.
This exercise is best exercise is to understand that how variable ports works and how the records processed through expression and ports. (Use Debugger and analyse it.)

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

Exercise 2: How to get (return) multiple values from unconnected Lookup?

Exercise 2: How to get (return) multiple values from unconnected Lookup?

Theory/Analysis:
As we already know when using unconnected lookup we can return only value with the help of return port. We can't select multiple port as return port. This constraint is imposed by Informatica itself.
We are smarter than Informatica, we will achieve it logically. Yes, you are right we need to override the default SQL query of Lookup. We will concatenate all required port value and would return it as a single value.

Practically: 

We will load DNAME and LOC value along with EMP table value in target. We will do lookup on Dept table in Unconnected Mode.

Step 1: Drag and Drop your source to Mapping Designer.

Step 2: 

Lookup

Create Lookup on Dept table and follow steps below..

A) Lookup-->Right Click-->Edit-->Port tab

     i. Create a port DEPTNO1 (Input Port)

     ii. Delete Loc port

     iii. DNAME port (Return Port and Lookup Port should be checked)

     iv. Increase the Prec of DNAME port to 50.

B) Lookup-->Right Click-->Edit-->Properties Tab

     Lookup Sql Override

    SELECT DEPT.DNAME||','||DEPT.LOC as DNAME, DEPT.DEPTNO as DEPTNO FROM DEPT

C) Lookup-->Right Click-->Edit-->Condition Tab

     Lookup Table Column   Operator  Transformation Port

                 DEPTNO                =              DEPTNO1

Step 3:

Expression

Create three ports

LKP_VALUE (Variable Port) = :LKP.LKPTRANS(DEPTNO)

Note: LKPTRANS is Lookup Transformation Name. If you have given different name please write that one.

DNAME (Output Port) = SUBSTR(LKP_VALUE, 1, INSTR(LKP_VALUE, ',', 1, 1)-1)

LOC (Output Port) = SUBSTR(LKP_VALUE, INSTR(LKP_VALUE, ',', 1, 1)+1)

Step 4: Connect your ports to target table.-

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

Exercise 1: How to Convert Char Code to Number Code

Let's Chat: I am creating a new label Exercise. In this section we will discuss the solution for all basic question. I don't feel that these questions should be treated like scenario because these questions are too basic and we are asking these type of questions in interview to judge candidate level and authenticity :-)
Just keep walking and one day you will be in my seat :-)

Exercise 1: How to Convert Char Code to Number Code

( Source)
CODE
----------
ABCD
BCDE
EFGH

(Target)
CODE
-------
1234
2345
5678

Analysis/Theory
After analyzing target we can conclude that number which are appearing in target those are serial number starting from 1 for letter A, so 26 would be for Z. we already know only one type of number we can get from letters that is ASCII and we don't see that value here so, we have to achieve this manually.
We have to get the number according to each and every letter... (Ok got it we can achieve this through single Expression Transformation only ... Let's do it Practically:

Practically:

Step 1: 
Expression
Create 10 ports (Columns)

FIRST_LETTER (Variable Port)= UPPER(SUBSTR(CODE,1,1))
SECOND_LETTER (Variable Port)= UPPER(SUBSTR(CODE,2,1))
THIRD_LETTER (Variable Port)= UPPER(SUBSTR(CODE,3,1))
FOURTH_LETTER (Variable Port)= UPPER(SUBSTR(CODE,4))

NUMBER_1 (Variable Port)= IIF(FIRST_LETTER='A', 1, IIF(FIRST_LETTER='B', 2, IIF(FIRST_LETTER='C', 3, IIF(FIRST_LETTER='D', 4,IIF(FIRST_LETTER='E', 5, IIF(FIRST_LETTER='F', 6,IIF(FIRST_LETTER='G', 7, IIF(FIRST_LETTER='H', 8, IIF(FIRST_LETTER='I', 9, IIF(FIRST_LETTER='J', 10, IIF(FIRST_LETTER='K', 11, IIF(FIRST_LETTER='L', 12, IIF(FIRST_LETTER='M', 13, IIF(FIRST_LETTER='N', 14, IIF(FIRST_LETTER='O', 15, IIF(FIRST_LETTER='P', 16, IIF(FIRST_LETTER='Q', 17, IIF(FIRST_LETTER='R', 18, IIF(FIRST_LETTER='S', 19, IIF(FIRST_LETTER='T', 20,IIF (FIRST_LETTER='U', 21, IIF(FIRST_LETTER='V', 22, IIF(FIRST_LETTER='W', 23, IIF(FIRST_LETTER='X', 24, IIF(FIRST_LETTER='Y', 25, IIF(FIRST_LETTER='Z', 26))))))) )))))))))))))))))))

NUMBER_2 (Variable Port)= IIF(SECOND_LETTER='A', 1, IIF(SECOND_LETTER='B', 2, IIF(SECOND_LETTER='C', 3, IIF(SECOND_LETTER='D', 4,IIF(SECOND_LETTER='E', 5, IIF(SECOND_LETTER='F', 6,IIF(SECOND_LETTER='G', 7, IIF(SECOND_LETTER='H', 8, IIF(SECOND_LETTER='I', 9, IIF(SECOND_LETTER='J', 10, IIF(SECOND_LETTER='K', 11, IIF(SECOND_LETTER='L', 12, IIF(SECOND_LETTER='M', 13, IIF(SECOND_LETTER='N', 14, IIF(SECOND_LETTER='O', 15, IIF(SECOND_LETTER='P', 16, IIF(SECOND_LETTER='Q', 17, IIF(SECOND_LETTER='R', 18, IIF(SECOND_LETTER='S', 19, IIF(SECOND_LETTER='T', 20,IIF (SECOND_LETTER='U', 21, IIF(SECOND_LETTER='V', 22, IIF(SECOND_LETTER='W', 23, IIF(SECOND_LETTER='X', 24, IIF(SECOND_LETTER='Y', 25, IIF(SECOND_LETTER='Z', 26))))))) )))))))))))))))))))

NUMBER_3 (Variable Port)= IIF(THIRD_LETTER='A', 1, IIF(THIRD_LETTER='B', 2, IIF(THIRD_LETTER='C', 3, IIF(THIRD_LETTER='D', 4,IIF(THIRD_LETTER='E', 5, IIF(THIRD_LETTER='F', 6,IIF(THIRD_LETTER='G', 7, IIF(THIRD_LETTER='H', 8, IIF(THIRD_LETTER='I', 9, IIF(THIRD_LETTER='J', 10, IIF(THIRD_LETTER='K', 11, IIF(THIRD_LETTER='L', 12, IIF(THIRD_LETTER='M', 13, IIF(THIRD_LETTER='N', 14, IIF(THIRD_LETTER='O', 15, IIF(THIRD_LETTER='P', 16, IIF(THIRD_LETTER='Q', 17, IIF(THIRD_LETTER='R', 18, IIF(THIRD_LETTER='S', 19, IIF(THIRD_LETTER='T', 20,IIF (THIRD_LETTER='U', 21, IIF(THIRD_LETTER='V', 22, IIF(THIRD_LETTER='W', 23, IIF(THIRD_LETTER='X', 24, IIF(THIRD_LETTER='Y', 25, IIF(THIRD_LETTER='Z', 26))))))) )))))))))))))))))))

NUMBER_4 (Variable Port)= IIF(FOURTH_LETTER='A', 1, IIF(FOURTH_LETTER='B', 2, IIF(FOURTH_LETTER='C', 3, IIF(FOURTH_LETTER='D', 4,IIF(FOURTH_LETTER='E', 5, IIF(FOURTH_LETTER='F', 6,IIF(FOURTH_LETTER='G', 7, IIF(FOURTH_LETTER='H', 8, IIF(FOURTH_LETTER='I', 9, IIF(FOURTH_LETTER='J', 10, IIF(FOURTH_LETTER='K', 11, IIF(FOURTH_LETTER='L', 12, IIF(FOURTH_LETTER='M', 13, IIF(FOURTH_LETTER='N', 14, IIF(FOURTH_LETTER='O', 15, IIF(FOURTH_LETTER='P', 16, IIF(FOURTH_LETTER='Q', 17, IIF(FOURTH_LETTER='R', 18, IIF(FOURTH_LETTER='S', 19, IIF(FOURTH_LETTER='T', 20,IIF (FOURTH_LETTER='U', 21, IIF(FOURTH_LETTER='V', 22, IIF(FOURTH_LETTER='W', 23, IIF(FOURTH_LETTER='X', 24, IIF(FOURTH_LETTER='Y', 25, IIF(FOURTH_LETTER='Z', 26))))))) )))))))))))))))))))

NUMERIC_CODE (Variable Port)= NUMBER_1||NUMBER_2||NUMBER_3||NUMBER_4

O_NUMERIC_CODE (Output Port)= TO_INTEGER(NUMERIC_CODE)

Step 3: Connect O_NUMERIC_CODE to Target

BINGO!!!

***LIMITATION: Solution above will work only for maximum code length four only.

Another Approach

If, we can create one table having letters and there number then we will escape from writing this much of code. Simply do lookup on that table and get the ID of that letter... In this approach we need few more transformation and this I am leaving upon you.. do some home work and Find Out  :-)

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