Add Isnull join condition to Azure data factory data flow

Question

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

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 .

Answer 1

To add col1 is not null in the join transformations condition, you can use cross join and then give the condition as and(source1@id == source2@id,not(isNull(name))). But it doesn't give all the rows of left table. As a workaround, you can use Join transformation for condition with same ids and filter transformation to check if the required field is null. I reproduced this with sample data. In this example, I want to do left join the source1 with source2 based on the id column and also to check if name is not null.

Source:1

id,name
1,Pasapugazh
2,Venkat
3,Senthil
4,Virumandi
5,
6,Pari

Source:2

id,age
1,21
2,22
3,32
4,35
5,42

• Join transformation is taken and Join type is given as left outer. Conditions is source1@id==source2@id

Output of Join:

• To remove the rows with null name, filter transformation is taken and condition is given as not(isNull(name))

Output

This removed the rows with null name. By this way, you can expand the joining conditions by join and filter transformations in data flow.

Answer 2

I created two branches (for the left joins on PT and BT) from the main data flow

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