joiner transformation query

Question

I have Flat File1 (F1) with these columns - key1, col1, col2 Flat File2 (F2) with these columns - key2, col1, col2 and one table (T1) with these columns - key3, col1, col2

Requirement is to get data from all 3 sources based on the below checks - when key1 in Flat file (F1) matches with key2 in Flat File(F2) - return all matching rows in F1 and F2 when key1 in Flat file (F1) doesnt matches with key2 in Flat File(F2) - Only then check should be done between flat file F1 and table T1 based on condition - key1 = key3 and if match is found - then return all matching rows in T1 and F1

To acheive teh above task

• I created Joiner traNSFORMATION between these 2 sources - F1 (Master) and F2 (Detail) and got the matching rows, and the join type that i selected was "Detail outer Join"
• Am stuck on how to do the remaining checks? can anyone please guide?

Answer 1

Cant we bring resultant outcome of both the sets of data to one common tranformation (like union) -> and from there we have to implement common logic. i.e.

• return all matching rows in F1 and F2
• the remaining unmatched rows of F1 should be joined with Table T1
• Finally the resultant outcome of the above 2 sets should be routed to one common tranformation (like union) -> and from there we have one common logic.

1. I have used joiner transf. to bring matching rows in F1 and F2 ->
2. used filter transf. with cond. to identify all unmatched rows of F1 with cond. Key2 is null ->
3. used joiner transf. to link table T1 with the records that were indetified as part of filter ->
4. The result identified as part of step1 and step3 are routed to Union But THere is an issue when we merge data using union transf. as we bringing data based on join type "Detail outer join" (due to which the data seem to get duplicated). How to get rid of this issue?

Answer 2

You can follow below steps

• First join FF1 and FF2 (Outer join FF2 so all data from FF1 comes in).
• Then use a router to group data that doesnt exist in FF2. You can send matching records to target (group 1).
• Non matching records can be picked when ff1.key is not null but ff2.key2 is null. Pick those records and match with Table T1 using a JNR.
• You can send these matching records to target.

Whole map should look like this -

sq_FF1 (master)                                                             |Grp 1 = ff1.key and ff2.key2 both NOT NULL (Matching)-------------------------------------------------> To TGT
       | JNR ( ff1.key=ff2.key2) (Detail outer join) --> ROUTER -(2 groups) |Grp 2 = ff1.key is NOT NULL and ff2.key2 IS NULL (NonMatching) -->  |
sq_FF2 (Detail)                                                                                                                                  | JNR key1 = key3 (inner join) ---> To TGT                   
sq_T1 -----------------------------------------------------------------------------------------------------------------------------------------> |