Blog

Funnels in DataStage

08 October, 2020

Related Blogs

Funnel

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

7Combining Source 1 and Source 1 without any key dependency

Funnel requirements

  1. 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 7 Random
  2. Sequence 7follows Linux order
  3. Sort funnel 7 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

Funnel

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 

output7Dump (link)à do Mapping

Now,7Output7Funnel  (link)7Do Mapping7Remove   DSlink 13.COMPANY / DSlink 13.COUNTRY7Go to  Columns7if any column Name changes have to be mode, do it here.

Funnel 

7funnel Type = Continuous funnel7Output7Do 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)

Data set  

Sequential file 1 

Load primary.txt

Sequential file 0 

Load ref.txt

In both  primary.txt and ref.txt   7Columns 7  State

lookup 

àMap State 7Drag and Drop Required Columns7Go to Constraints7Set reject  

Column Generator 

Column to Generate      = STATE – DESC7OutputàDo Mapping7Columns7Right click on STATE – DESC7Algorithm = Cycle7Value   = not applicable7Apply7Close 7Compile 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

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 

7While 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 7Input column   7primary

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

 

  1. job

Job:- (Not Applicable)

Constraints    

Sequential file  0          

 Load primary.txt 7columns 7 state

  Sequential file 1

Load ref.txt  7 columns7state  

Sequential file 2 

Load rfe2.txt 7 columns 7 state  

Ref2 

Status Status- desc
1001 Married
1000 Divorced
1111 Single

  Lookup

  7Drag State from primary to ref

7Drag Status from primary to ref 1

7Now, Drag and Drop required Columns7Click on Constraints  

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

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

7Now, 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

For an in-depth Knowledge on DataStage, click on below