0

I am trying to filter out the null record from previous lookup activity in azure synapse workspace. To do this, I have used the below mentioned expressions in filter activity. But I am not able to get rid of the null records in the filter activity output. I have used the coalesce() function also to do that.

FYI, I know there is IsNull expression in data flow activity, but I don't want to use Data flow in pipeline as the pipeline is small and dataflow execution is extra cost.

PFB, details and attached screenshot. enter image description here enter image description here

Approach used : Filter activity to filter null records using coalesce() function and without coalesce(). Expression:

  1. @if(equals(coalesce(activity('LookupID').output.value[0].ID,''),''), true,false)
  2. @if(equals(activity('LookupID').output.value[0].ID, null), false,true)
  3. @if(equals(activity('LookupID').output.value[0].ID, ''),''), false,true)

With any of the above expressions, the filter output has all the records including null value records. Please share your suggestion if I am missing anything.

sac
  • 175
  • 2
  • 14

1 Answers1

0

The reason is because the filter condition. You can use the following condition instead to filter out null records returned from look up activity.

  • I have a look up activity called Lookup id which returns id and gname where 1 id record is null and 2 are not null.

enter image description here

  • Now I have used the following dynamic content as the items and condtion for filter activity:
items: @activity('Lookup id').output.value

conditions: @if(equals(item().id, null), false,true)

enter image description here

  • The filter would work as expected and the output would be as shown below (only two records returned):

enter image description here

Saideep Arikontham
  • 5,558
  • 2
  • 3
  • 11