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 idE NameAdd
33BHYD
44CDEL
11AHYD
22ESEC

   

E idE NameGenderSalAddCountryCompany
33B123000HYDaaaaaa
44C022000DELbbbBbb

  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 idE NameStateStatusState - desc
11SUMANAP1001Andhra Pradesh
22MOONTN1000Tamil Nadu
33SUMANAP1111Andhra Pradesh
55SUMANAP1101Andhra Pradesh
66MOONJK1001Jammu Kashmir
77MOONSUNAP1011Andhra Pradesh

E idE NameStateStatusState - desc
44MOONSTARNN1011Not Applicable
88MOONMP1111Not Applicable

  Data set 1

data set 1  

Output 

E idE NameStateStatusState - desc
11SUMANAP1001Andhra Pradesh
22MOONTN1000Tamil Nadu
33SUMANAP1111Andhra Pradesh
55SUMANAP1101Andhra Pradesh
66MOONJK1001Jammu Kashmir
77MOONSUNAP1011Andhra Pradesh
44MOON STARNN1011Not Applicable
88MOONMP1111Not 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 

StatusStatus- desc
1001Married
1000Divorced
1111Single

  Lookup

  7Drag State from primary to ref

7Drag Status from primary to ref 1

7Now, Drag and Drop required Columns7Click on Constraints  

Link NameconditionCondition Not metLookup failure

  1. ref

DropDrop
2.ref1 DropDrop

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

7Now, Compile And RUN  

Output

E idE NameStateStatusState - desc 
11SUMANAP1001Andhra PradeshMarried
33SUMANAp1111Andhra PradeshSingle

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