topic Re: Filter file based on records on table in Talend Studio

Filter file based on records on table

Karuetl — Wed, 05 Dec 2018 14:54:35 GMT

I have 10000 rows for example from table

And I have another flow which is input file and i want to filter out those ID (From above step) that are present in the file

Example

--------------

Table A

Select ID from Table A ;

123

672

888

Input File

abcve,672,newjersey

jkuijk,999,california

output should be

File

jkuijk,999,california

Re: Filter file based on records on table

David_Beaty — Wed, 05 Dec 2018 15:52:34 GMT

Hi,

Depending on the database in use, upload the ID from the file into a temporary table and then select the rows from the database table A where they do/do not exist in the temporary table.

Thanks

David

Re: Filter file based on records on table

Karuetl — Wed, 05 Dec 2018 16:08:06 GMT

There are multiple files with different schema among each and those has ID

I want to each file check with those ID from the table and then filter out

Re: Filter file based on records on table

Ganshyam — Thu, 06 Dec 2018 11:34:20 GMT

Hello,

Try performing joins on table and input file.

Apply filter on second tmap and filter the values that have null values. out1.ID_1 == null

Regards

Ganshyam Patel

Re: Filter file based on records on table

Anonymous — Thu, 06 Dec 2018 12:55:31 GMT

@Karuetl @Ganshyam

You can do it in single tmap as shown below (you can remove tLogrow since it has been added for testing)

The output is as shown below.

The trick lies in using the inner join and catching the rejected inner join records from the tMap.

If the answer has helped you, could you please mark the topic s resolved? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi

Re: Filter file based on records on table

Karuetl — Thu, 06 Dec 2018 14:20:59 GMT

What is the join condition logic

Re: Filter file based on records on table

Karuetl — Thu, 06 Dec 2018 14:21:48 GMT

You have declared the column name and you know the position , in my case I dont know the position and i read the file entire row as string

Re: Filter file based on records on table

Ganshyam — Thu, 06 Dec 2018 14:48:21 GMT

Hello,

You can use tfileinputfullrow component to read entire row as a string and then split into 3 field using textractdelimited component and further you can continue with joins.

Re: Filter file based on records on table

Karuetl — Thu, 06 Dec 2018 14:49:34 GMT

As i mentioned its dynamic columns , it is not fixed like 3 columns or so

one file can have 10 columns other file can have 6 columns

Re: Filter file based on records on table

Ganshyam — Tue, 11 Dec 2018 13:59:44 GMT

Hello,

Not sure , but you can try using tjavarow and some java code to handle the dynamic input column.

Regards

Ganshyam Patel