CODEWITHSUNDEEP
informatica
John
John

Reputation: 29

How to do a joining for making decision from 2 different filter

There are total 4 tables invoked in this mapping: Market,Cost, A, B,

Read_sourceTB_B-----FIL1------->---------JNR4 \
    |                                     |     |
    |    Read_sourceTB_Market--\          |     |
    |    Read_sourceTB_Cost------JNR1--\  |     |
    |    Read_sourceTB_A-----------------JNR2   JNR5--->EXP... -->TGT
    |                       |             |     |
    |                       |             |     |
    |                       |             |     |
     ---------------------FIL2->---------JNR3 /

How to make a decision for if A join B System_Code='University'failed, then A join B System_Code='Policy'

--First joinning condition  
A LEFT JOIN B 
ON A.MEMBERSHIPID = B.MEMBERSHIPID
Where B.System_Code='University'

IF <First joinning condition> failed, then execute

--Second joinning condition
A LEFT JOIN B ON 
A.address = B.address and A.phonenumber = B.phonenumber

Where B.System_Code='Policy'

Upvotes: 1

Views: 139

Answers (2)

Koushik Roy
Koushik Roy

Reputation: 7387

You need to join A with B (twice) based on two different condition and then join them back to one single pipeline for a decision/if-else condition. Also please note, all your left joins are actually inner join because you are using B.xxx='something' condition in the where clause.

So, considering above problem -

  1. After source qualified of B, add two filters FIL1(system_Code='University') and FIL2(System_Code='Policy') in parallel.

  2. Then use JNR1 to join A and B(FIL1) using JOINER on A.MEMBERSHIPID = B_F1.MEMBERSHIPID. Use A as detail table and use 'inner join'.

  3. Then join A and B(FIL2) using JOINER(JNR2) on A.address = B_F2.address and A.phonenumber = B_F2.phonenumber. Use A as detail table and use 'inner join'.

  4. Then join above two pipelines into one single pipeline using another Joiner(JNR3). It should be normal join and join should be primary key from table A. Get all required columns.

  5. (EXP)Then use an expression transformation. Use logic similar to below.

out_col1 = IIF( isnull(col_tableB_F1_jnr1),col_tableB_F2_jnr2, col_tableB_F1_jnr1)

Whole mapping should look like this -


SQ_TABLEB --FIL1-> -- JNR1 \
        |               |   |
        |   SQ_TABLEA --|    JNR3-->EXP.... -->TGT  
        |               |   | 
        |--FIL2-> -- JNR2  /

But i think your requirement may be like this -

A LEFT JOIN B 
ON A.MEMBERSHIPID = B.MEMBERSHIPID AND B.System_Code='University'

if yes, then change the inner join to master outer join in the JNR1 and JNR2.

Upvotes: 1

Maciejg
Maciejg

Reputation: 3353

Read the B data source twice, this should be something like:

Read_sourceTB_Market--\         
Read_sourceTB_Cost------JNR1--\
Read_sourceTB_A-----------------JNR2-\
Read_sourceTB_B1-----FIL1---->--------JNR4--\
Read_sourceTB_B2-----FIL2---->---------------JNR5--->EXP... -->TGT

Upvotes: 1

Related Questions