**Q1.**The source data contains only column 'id'. It will have sequence numbers from 1 to 1000. The source data looks like as

Id

1

2

3

4

5

6

7

8

....

1000

Create a workflow to load only the Fibonacci numbers in the target table. The target table data should look like as

Id

1

2

3

5

8

13

.....

In Fibonacci series each subsequent number is the sum of previous two numbers. Here assume that the first two numbers of the fibonacci series are 1 and 2.

**Solution:**

**STEP1:**Drag the source to the mapping designer and then in the Source Qualifier Transformation properties, set the number of sorted ports to one. This will sort the source data in ascending order. So that we will get the numbers in sequence as 1, 2, 3, ....1000

**STEP2:**Connect the Source Qualifier Transformation to the Expression Transformation. In the Expression Transformation, create three variable ports and one output port. Assign the expressions to the ports as shown below.

Ports in Expression Transformation:

id

v_sum = v_prev_val1 + v_prev_val2

v_prev_val1 = IIF(id=1 or id=2,1, IIF(v_sum = id, v_prev_val2, v_prev_val1) )

v_prev_val2 = IIF(id=1 or id =2, 2, IIF(v_sum=id, v_sum, v_prev_val2) )

o_flag = IIF(id=1 or id=2,1, IIF( v_sum=id,1,0) )

**STEP3:**Now connect the Expression Transformation to the Filter Transformation and specify the Filter Condition as o_flag=1

**STEP4:**Connect the Filter Transformation to the Target Table.

**Q2.**The source table contains two columns "id" and "val". The source data looks like as below

id val

1 a,b,c

2 pq,m,n

3 asz,ro,liqt

Here the "val" column contains comma delimited data and has three fields in that column.

Create a workflow to split the fields in “val” column to separate rows. The output should look like as below.

id val

1 a

1 b

1 c

2 pq

2 m

2 n

3 asz

3 ro

3 liqt

**Solution:**

**STEP1:**Connect three Source Qualifier transformations to the Source Definition

**STEP2:**Now connect all the three Source Qualifier transformations to the Union Transformation. Then connect the Union Transformation to the Sorter Transformation. In the sorter transformation sort the data based on Id port in ascending order.

**STEP3:**Pass the output of Sorter Transformation to the Expression Transformation. The ports in Expression Transformation are:

id (input/output port)

val (input port)

v_currend_id (variable port) = id

v_count (variable port) = IIF(v_current_id!=v_previous_id,1,v_count+1)

v_previous_id (variable port) = id

o_val (output port) = DECODE(v_count, 1,

SUBSTR(val, 1, INSTR(val,',',1,1)-1 ),

2,

SUBSTR(val, INSTR(val,',',1,1)+1, INSTR(val,',',1,2)-INSTR(val,',',1,1)-1),

3,

SUBSTR(val, INSTR(val,',',1,2)+1),

NULL

)

**STEP4:**Now pass the output of Expression Transformation to the Target definition. Connect id, o_val ports of Expression Transformation to the id, val ports of Target Definition.

For those who are interested to solve this problem in oracle sql, Click Here. The oracle sql query provides a dynamic solution where the "val" column can have varying number of fields in each row.

very nice

ReplyDeleteSuperb sir. u have done gud job. explanation gives simple to understand. Thanks

ReplyDeleteHi, thanks for all your excellent work...I learn alot!

ReplyDeleteBut I am not able to understand the first question's ans, Which port is the final port that need to be connected to target for the desired result?

'sum' port needs to be connected to target, as that port is calculating the numbers of Fibonacci series

DeleteConnect id and o_val ports from expression transformation to the target

ReplyDeletesimply superb job gentlemen...hatsoff

ReplyDeleteCan u Solve this plz

ReplyDeletesource

Rama

rajesh

anu

sonu

suryanarayana

Target

Rama*********

rajesh*******

anu*********

sonu*********

suryanarayana

BY USING STORED PROCEDURE TRANSFORMATION IT IS POSSIBLE.

Deletelook at this logic

create or replace procedure string_lpad(name in varchar2,out_name out varchar2) is

v_max number;

begin

select max(length(name)) into v_max from source_tab_name;

out_name:=rpad(name,v_max,'*');

end;

@infguy,

ReplyDeleteWhat i understood from the question is you need the output as a 13 character string. If the input string has less than 13 characters, then you want to add * at the end of the string.

If my assumption is correct, then it is very easy to solve using expression transformation.

Create an output port in expression transformation and then assign the expression rpad(string,13,'*')

If this is not what you want, then please clearly explain your problem

@smile

ReplyDeleteThanks

yes exactly i want to put * if the string is less than the highest string length in source but how can v know the highest one is 13 i mean if v dont know the highest size(if v hv millions in source)

can u explain me the solution step by step

ReplyDelete@infguy

ReplyDeleteOne thing you can do is write the length of the maximum length of the string in a parameter file in one session.

SELECT max(length(string)) from table_name;

In the other session read the parameter value in expression transformation.

I never tried this one. I hope it will work for you.

Hi

ReplyDeleteI am having a problem

i am passing dept table to router and giving three conditions to three groups deptno=10,deptno=20,deptno=30

requirement is if first group condition is satisfied i dnt want to run remaining groups or they should fail

can u help me in this

@infguy

ReplyDeleteIf you get detpno=10 in the input, then other two group conditions will definitely fail.

This comment has been removed by the author.

ReplyDeleteHow router transformation works is, it will check all the group conditions and if they pass, then it pass the corresponding group data to the downstream transformations.

ReplyDeleteFor example if you specify three group conditions as deptno<=10, deptno<=20, deptno<=30. When you get deptno=15 as input, then the 2nd and 3rd groups will satisfy.

@vijay bhaskar

ReplyDeleteThanks for ur reply i understand the router functionality but my scenario is whenever the first group condition( say deptno=10) satisfied the remaining group conditions( if any) should fail.

@infguy

ReplyDeleteIn your scenario, the other two group conditions will fail.

@ vijay bhaskar

ReplyDeletewht should be the condition in 2 group to fail it

If you want to fail the other groups always, use some department number which is not in the database at all. something like deptno=999999999

ReplyDeleteHi

ReplyDeletesource is

abc

count

and the target should load as

abc

abc

.

.

count times

if the count is 10 then i want to load abc 10 times

Can u help me doing this

In informatica i am not sure how to do this. May be you have to use java transformation. You can implement this suing a procedure. If you are using oracle data base, please go through the link Query to duplicate column data

DeleteAs per my knoledge on Informatica there are multiple solutions...

Delete1. If you know the max count then using normalizer you can achieve it.

2. If you don't know the max count then...

2a. Use SQL transformation. OR

2b. Use Stored Procedure transformation.

2c. Use Java transformation.

Let me know if you need detailed solutions for it. (Contact me @ karun4all@gmail.com

Hope this gives you some idea, thank you. -- Karun

Your Explanation is really awasome

ReplyDeleteThaks

Rajneesh

Your Scenerios and the explanation is really amazing and so helpful... The scenerios help understand the transformations and their properities even more...

ReplyDeleteIf possible please post some more scenerios as well

Much appereciated!

Cheers!

Manasvini

For q1...

ReplyDeleteOut_put = DECODE(NO_VALUE,1,1,2,2,Prev_No + PRE_PREV_NO)

PRE_PREV_NO = Prev_No

Prev_No = Out_put

Out_put := DECODE(NO_VALUE,1,1,2,2,Prev_No + PRE_PREV_NO)

ReplyDeletePRE_PREV_NO := Prev_No

Prev_No := Out_put

Generate Fibonacci Series:

ReplyDeletePorts in Expression Transformation:

ID

V_FIB1 = IIF(ID<3, ID, V_FIB2)

V_FIB2 = MOVINGSUM(V_FIB1, 2)

O_Fibonacci_Num

Connect Expression transformation's output port (ID & O_Fibonacci_Num) to the Filter transformation.

Condition: ID>10

Note: Use Filter when you want to print the Fibonacci number till the specific ID number (eg. 10) else you can directly connect Expression transformation's output to Target.

Hope this will help.

The fibbonacci can be solved by just 3 steps na, just see

ReplyDeleteCurr_id = id

O_id = curr_id + prev_id

Prev_id = curr_id

This will work i guess

## Post a Comment