filter on jobid

Vineet S 205

Hi,

if we have 2 table table1 =jobid

and table2- jobid,jobname

i want to filter the data in table 1 where jobid not equal to jobname='test1'

1 answer

Gowtham CP 1,970 Reputation points

2024-05-15T06:22:46.58+00:00

Hello @Vineet S

Thanks for reaching out in the Microsoft Q&A!

To filter table1 data in Azure Data Factory (ADF) based on jobid not equal to jobname='test1' in table2, use the Filter transformation. Set up two Source datasets for table1 and table2, with a Sink dataset for filtered data. Add a Filter transformation after table1, defining the condition "NOT table1.jobid == lookup(table2, 'jobid', 'jobname', 'test1')". This filters out rows from table1 where jobid doesn't match jobid associated with jobname='test1' in table2. Optionally, consider the Merge transformation if you need data from both tables, or Filter if only data from table1 is required.

Don't forget to upvote! If you found this solution helpful, consider accepting it.
Please sign in to rate this answer.
Vineet S 205 Reputation points

2024-05-15T06:38:04.5233333+00:00

how we can add before the sink in existing pipeline

Vineet S 205 Reputation points

2024-05-15T08:11:47.55+00:00

how we can add before the sink or target in existing pipeline

Gowtham CP 1,970 Reputation points

2024-05-15T08:35:23.39+00:00

To add filtering before the sink in your Azure Data Factory pipeline, follow these steps: locate the final transformation, find the start of 'table1' processing, insert a Lookup and a Filter between 'table1' and the sink, connecting 'table1' to Filter's first input and Lookup's output to its second. The Filter's expression should be 'NOT table1.jobid == lookup(table2, 'jobid', 'jobname', 'test1')'. This creates a neat structure: 'table1' -> Filter (new) -> Lookup (new) -> Existing transformations -> Sink. With this setup, you ensure only the relevant data, meeting your filtering criteria, progresses through the pipeline to the sink, optimizing workflow efficiency.

Vineet S 205 Reputation points

2024-05-15T08:48:50.69+00:00

Can you share me the screenshot like sample

Vineet S 205 Reputation points

2024-05-15T08:49:06.6033333+00:00

Or a digram

Vineet S 205 Reputation points

2024-05-15T08:52:18.7233333+00:00

Currently table1 is not there and data is coming from excel sheet and needs to filter and lookup with table 2 jobid and job name is test

Gowtham CP 1,970 Reputation points

2024-05-15T13:41:11.29+00:00

Hello Vineet S,

Please refer to the diagram provided for your reference. If you find the solution helpful, please remember to upvote and accept it.

Vineet S 205 Reputation points

2024-05-15T14:08:11.9366667+00:00

Want to do It after cast2

Vineet S 205 Reputation points

2024-05-15T14:13:55.41+00:00

want to do it after cast1 and filter

Gowtham CP 1,970 Reputation points

2024-05-15T14:14:01.4533333+00:00

I'm sorry for the confusion. Could you please clarify your statement so I can better understand what you're asking?

Vineet S 205 Reputation points

2024-05-15T14:19:02.1733333+00:00

can i have your gmail id to quick chat....

in below screenshot 2 tables are loaded one is with filter condition and 2nd in without filter condition

then i want lookup on the table2 lookup and filter the data and then load it

Vineet S 205 Reputation points

2024-05-15T16:02:44.2466667+00:00

can i have your gmail id pls ... we can have quick chat

there are 2 tables in sink and should to 1st sink on the top right side in which already one filter is applied

Vineet S 205 Reputation points

2024-05-16T03:46:21.93+00:00

Any new updates

Vineet S 205 Reputation points

2024-05-16T04:26:44.1733333+00:00

Any new updates

Vineet S 205 Reputation points

2024-05-16T06:27:40.5933333+00:00

Thanks... I got can your look before sink also right

Smaran Thoomu 10,880 Reputation points Microsoft Vendor

2024-05-16T08:05:25.3633333+00:00

Vineet S Can you confirm if your issue has been resolved?

Vineet S 205 Reputation points

2024-05-16T09:26:21.7166667+00:00

not yet unable to add filter condition with another table... can you help with code related to filter transformation first then lookup... please refer to below diagram and assist ... pls remember it is dataflow

Vineet S 205 Reputation points

2024-05-16T10:00:47.46+00:00

Please help me with filter transformation in dataflow

Vineet S 205 Reputation points

2024-05-16T10:01:42.0533333+00:00

filter transformation from table2 in adf

Smaran Thoomu 10,880 Reputation points Microsoft Vendor

2024-05-17T04:56:37.7166667+00:00

Hi @Vineet S

You can use the Filter transformation in Azure Data Factory Mapping Data Flow to filter the data in Table1 based on the condition where jobid is not equal to jobname='test1'. Here's the code snippet for the Filter transformation:

Table1 filter( jobid != 'test1' ) ~> FilteredTable1

This code will filter the data in Table1 where jobid is not equal to 'test1' and output the filtered data to a new stream called FilteredTable1.

Next, you can use the Lookup transformation to join the filtered data from Table1 with the data from Table2 based on the jobid column. Here's the code snippet for the Lookup transformation:

FilteredTable1 innerJoin( Table2, ['jobid'], ['jobid'] ) ~> JoinedData

This code will join the filtered data from Table1 with the data from Table2 based on the jobid column and output the joined data to a new stream called JoinedData.

I hope this helps! Let me know if you have any further questions.
Sign in to comment

Share via

filter on jobid

1 answer