Funnel in DataStage

Funnel

  It is a processing stage that supports n inputs and 1 output.

Combining Source 1 and Source 1 without any key dependency

 

Funnel requirements

1. The structure of Source 1 and Source 2 should be the same
2. Column Names should be the same in Source 1 and Source 2.

 

Funnel types 

We have 3 Funnel types

1. Continuous funnel  Random
2. The sequencefollows Linux order
3. Sort funnel  based on key column values

 

Copy 

It is having multi-purpose

• Makes Source to Multiple copies
• Change the column Names
• Drop the columns
• used as Stub Stage.

 

job:- funnel, copy Generator, and copy

 

Sequential file 0

 ↓

Load

  ↓

E id             E Name      Add

11                   A             HYD

22                    E             SEC  

 

Sequential file 1

E id         E Name    Gender         Sal                 Add

33              B                 1                 23000             HYD

22             C                  0                 22000               Del  

 

Column Generator 

Column to Generate        = COUNTRY

Column to Generate        = COMPANY

Output

Do Mapping    (link 13)  

 

Copy 

outputDump (link)à do Mapping

Now,OutputFunnel  (link)Do MappingRemove   DSlink 13.COMPANY / DSlink 13.COUNTRYGo to  Columnsif any column Name changes have to be mode, do it here.

 

Funnel 

funnel Type = Continuous funnelOutputDo Mapping

 

Output 

Data set 0

E id	E Name	Add
33	B	HYD
44	C	DEL
11	A	HYD
22	E	SEC

   

E id	E Name	Gender	Sal	Add	Country	Company
33	B	1	23000	HYD	aaa	aaa
44	C	0	22000	DEL	bbb	Bbb

  Data set

 

Job:-  (Not Applicable)

 

 

Sequential file 1 

Load primary.txt

 

Sequential file 0 

Load ref.txt

In both  primary.txt and ref.txt   Columns   State

 

lookup 

àMap State Drag and Drop Required ColumnsGo to ConstraintsSet reject  

 

Column Generator 

Column to Generate      = STATE – DESCOutputàDo MappingColumnsRight click on STATE – DESCAlgorithm = CycleValue   = not applicableApplyClose Compile And RUN

output 

 

Data set 0

E id	E Name	State	Status	State - desc
11	SUMAN	AP	1001	Andhra Pradesh
22	MOON	TN	1000	Tamil Nadu
33	SUMAN	AP	1111	Andhra Pradesh
55	SUMAN	AP	1101	Andhra Pradesh
66	MOON	JK	1001	Jammu Kashmir
77	MOONSUN	AP	1011	Andhra Pradesh

 

E id	E Name	State	Status	State - desc
44	MOONSTAR	NN	1011	Not Applicable
88	MOON	MP	1111	Not Applicable

 

Data set 1

 

 

Output 

E id	E Name	State	Status	State - desc
11	SUMAN	AP	1001	Andhra Pradesh
22	MOON	TN	1000	Tamil Nadu
33	SUMAN	AP	1111	Andhra Pradesh
55	SUMAN	AP	1101	Andhra Pradesh
66	MOON	JK	1001	Jammu Kashmir
77	MOONSUN	AP	1011	Andhra Pradesh
44	MOON STAR	NN	1011	Not Applicable
88	MOON	MP	1111	Not Applicable

 

Inclined to build a profession as Datastage Developer? Then here is the blog post on, explore Datastage Training

 

Conditions 

While using lookup, We Can Write the conditions in Constraints

That is Generally when we take 2 sequential files

1.primary and 1 reference file, we can Write Conditions for the only the primary file

 

Constraints

Link Name               Condition           Condition Not Met                   Lookup failure

Ref                                                                              Drop                                      Drop

Right-click Input column   primary

• If we need to write Conditions for reference file then, take 3 Sequential files
• That is 1 primary, reference file, reference file i

 

4. job

 

Job:- (Not Applicable)

   

Sequential file  0          

 Load primary.txt columns  state

  Sequential file 1

Load ref.txt   columnsstate  

Sequential file 2 

Load rfe2.txt  columns  state  

Ref2 

Status	Status- desc
1001	Married
1000	Divorced
1111	Single

 

Lookup

  Drag State from primary to ref

Drag Status from primary to ref 1

Now, Drag and Drop required ColumnsClick on Constraints  

Link Name	condition	Condition Not met	Lookup failure
ref	Drop	Drop
2.ref1		Drop	Drop

  Right clickInput Column ref ref. State –desc  = “Andhra  Pradesh”

Now, Compile And RUN  

 

Output

E id	E Name	State	Status	State - desc
11	SUMAN	AP	1001	Andhra Pradesh	Married
33	SUMAN	Ap	1111	Andhra Pradesh	Single