3 Replies Latest reply on May 14, 2021 12:21 AM by JanLeendert Wijkhuijs

    joiner transformation query

    AW Khan New Member

      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?
        • 1. Re: joiner transformation query
          JanLeendert Wijkhuijs Seasoned Veteran

          Hi,

          After the joiner you can add a router transformation in which you can add 2 groups.
          Group1: Key2 is null
          Group2: Key2 is not null

          The ports of F1 should be dragged from Group1 to next router where you join with T1
          The required ports of F1 F2 should be connected to next transformation depending on your further requirements.

          Hope this helps.

           

          Regards

          JanLeendert

          • 2. Re: joiner transformation query
            AW Khan New Member

            The idea is to 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

             

             

            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?

            • 3. Re: joiner transformation query
              JanLeendert Wijkhuijs Seasoned Veteran

              Hi,

              Are you sure you don't have any duplicates in the sources?
              Especially the flat files could be investigated for it but also the table T1 if there isn't a primary key defined on it.

               

              Regards,
              JanLeendert