Informatica: How to make a condition for two tables joining execution

Question

Actually, 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 /

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

**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'

Which transformation should I use? I don't know how to use Informatica, my version is Informatica Developer 10.5, please help me.Thanks!

I only know how to

A left join B on `condition`  `System_Code='University'`

left join B on `condition`   `System_Code='Policy'`

but I don't know how to make a decision for if A join B System_Code='University'failed,

then A join B System_Code='Policy'

Answer 1

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.