azure-data-factoryazure-mapping-data-flowazure-synapse-pipeline

Add Isnull join condition to Azure data factory data flow

I have this sql script : I need to add the "ON UpdatedWOV.Id IS NULL AND CTH.Id = PT.TranId" to a dataflow. My challenge is specifically how to add "ON UpdatedWOV IS NULL"

please note that there are other join conditions that doesn't require the filter;

left join PT
ON UpdatedWOV.Id is null
AND CTH.Id = PT.TranId
left join BT 
on UpdatedWOV.Id is null  
and CTH.Id = BT.TranId
left join etl.PmsRecord 
on CTH.PMSRecordId = PmsRecord.Id 
left join etl.PMSExpenseRecord 
on CTH.PMSExpenseRecordId = PMSExpenseRecord.Id 
left join etl.PMSCommissionRecord 
on CTH.PMSCommissionRecordId = PMSCommissionRecord.Id

Data flow screenshot

I tried to add a filter with the expression "isNull(UpdatedWOV.Id)" in the data flow right before applying the left join "CTH.Id = PT.TranId" but that filters out all the valid records .

Solution

  • I created two branches (for the left joins on PT and BT) from the main data flow enter image description here

    enter image description here

    and applied my filter on each branch that required "UpdatedWOV.Id" to be null (isNull(UpdatedWOV.Id))

    I finally integrated the results of each filter back to the main data flow using the same join conditions;

    left join PT AND CTH.Id = PT.TranId

    left join BT
    and CTH.Id = BT.TranId

    enter image description here